from fastapi import APIRouter, Depends, HTTPException, Query
from fastapi.security import HTTPAuthorizationCredentials, HTTPBearer
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, desc, asc, func, text
from sqlalchemy.sql import exists
from typing import List, Optional
from datetime import datetime
import math
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

from database.connection import get_db
from utils.auth import verify_token, get_current_user_id, security
from utils.zhipu_content_check import check_sensitive_content
from database.models import User, Competition, CompetitionWork, WorkComment, CommentLike, UserFollow, WorkLike, UserNotification
from models.competition import (
    CompetitionCreate, CompetitionUpdate, CompetitionResponse, CompetitionListResponse, CompetitionQuery,
    CompetitionWorkCreate, CompetitionWorkUpdate, CompetitionWorkResponse, CompetitionWorkListResponse, CompetitionWorkQuery,
    WorkScoreRequest, WorkLikeRequest, CompetitionStatusRequest, WorkAuditRequest, WorkSortRequest, WorkSortResponse,
    WorkCommentCreate, WorkCommentUpdate, WorkCommentResponse, WorkCommentListResponse, WorkCommentQuery, CommentLikeRequest, CommentDeleteRequest, CommentResponse, WorkCommentListUnifiedResponse
)
from models.competition_score import ScoreCreate, ScoreResponse
from database.models import ScoreDimensions, DimensionScoreRules, WorkJudgeDimensionScore
from database.connection import engine
from utils.files import safe_replace_file

router = APIRouter(tags=["比赛管理"])


# ========== 比赛管理接口 ==========

def safe_parse_json_list(text_value):
    """
    安全解析存储为 Text 的 JSON 列表
    
    Args:
        text_value: 文本字段，期望为 JSON 数组或 None
    Returns:
        list[str]: 正常解析得到的字符串列表，非法或空返回空列表
    """
    import json
    if not text_value:
        return []
    try:
        obj = json.loads(text_value) if isinstance(text_value, str) else text_value
        if isinstance(obj, list):
            return [str(x) for x in obj]
        return []
    except (json.JSONDecodeError, TypeError):
        return []

@router.get("/competitions/score-rules", summary="获取比赛评分维度与规则（全部）")
async def get_competition_score_rules(db: Session = Depends(get_db)):
    """
    获取全部评分维度及其对应的规则（不分页、不筛选），
    返回结构与 /v1/score-dimensions/rules/all 接口保持一致。

    Returns:
        {code, msg, data: {dimensions_with_rules, pagination, summary}}
    """
    try:
        dimensions = db.query(ScoreDimensions).order_by(ScoreDimensions.id.asc()).all()
        total_dimensions = len(dimensions)

        dimensions_with_rules = []
        total_rules_count = 0

        for dimension in dimensions:
            rules = db.query(DimensionScoreRules).filter(
                DimensionScoreRules.dimension_id == dimension.id
            ).order_by(DimensionScoreRules.score_level.asc()).all()
            total_rules_count += len(rules)

            dimension_rules = [
                {
                    "id": rule.id,
                    "dimension_id": rule.dimension_id,
                    "score_level": rule.score_level,
                    "level_desc": rule.level_desc,
                    "judge_criteria": rule.judge_criteria,
                    "create_time": rule.create_time.strftime("%Y-%m-%d %H:%M:%S") if rule.create_time else None
                }
                for rule in rules
            ]

            dimensions_with_rules.append({
                "dimension_info": {
                    "id": dimension.id,
                    "dimension_name": dimension.dimension_name,
                    "description": dimension.dimension_desc,
                    "weight": float(dimension.dimension_weight),
                    "create_time": dimension.create_time.strftime("%Y-%m-%d %H:%M:%S") if dimension.create_time else None
                },
                "rules": dimension_rules,
                "rules_count": len(dimension_rules)
            })

        response_data = {
            "dimensions_with_rules": dimensions_with_rules,
            "pagination": {
                "current_page": 1,
                "page_size": total_dimensions,
                "total_dimensions": total_dimensions,
                "total_pages": 1
            },
            "summary": {
                "total_dimensions": total_dimensions,
                "current_page_dimensions": total_dimensions,
                "total_rules": total_rules_count
            }
        }

        return {"code": 200, "msg": "查询成功", "data": response_data}
    except Exception as e:
        return {"code": 500, "msg": f"查询失败: {str(e)}"}


@router.get("/competitions/{competition_id}/works/score-status", summary="按评分状态查询作品")
async def get_competition_works_by_score_status(
    competition_id: int,
    type: str = Query("全部", description="评分状态：未评分/已评分/全部"),
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(10, ge=1, le=100, description="每页数量"),
    db: Session = Depends(get_db)
):
    """
    按评委维度评分记录统计的平均分，查询并筛选作品列表

    参数:
    - competition_id: 比赛ID
    - type: 评分状态，未评分/已评分/全部（按是否存在评委评分记录判断）
    - pageNum: 页码（默认1）
    - pageSize: 每页数量（默认10，最大100）

    返回:
    - 返回比赛基础信息：`competition`
    - 分页作品列表：`items`，包含 `judge_count` 与 `avg_score`（两位小数）
    - 对于已评分的作品，补充 `score_detail`：包含全部评分维度的平均打分与加权得分，以及作品总分 `total_score`
    """
    try:
        comp = db.query(Competition).filter(Competition.id == competition_id, Competition.delete_time.is_(None)).first()
        if not comp:
            return {"code": 404, "msg": "比赛不存在", "data": None}

        base_q = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.release_status == 1,
            CompetitionWork.delete_time.is_(None)
        )

        if type == "未评分":
            base_q = base_q.filter(~exists().where(WorkJudgeDimensionScore.work_id == CompetitionWork.id))
        elif type == "已评分":
            base_q = base_q.filter(exists().where(WorkJudgeDimensionScore.work_id == CompetitionWork.id))

        total = base_q.count()
        base_q = base_q.order_by(desc(CompetitionWork.work_sort), desc(CompetitionWork.create_time))
        offset = (pageNum - 1) * pageSize
        works = base_q.offset(offset).limit(pageSize).all()
        pages = math.ceil(total / pageSize) if total > 0 else 1

        work_ids = [w.id for w in works]
        avg_map = {}
        cnt_map = {}
        # 作品总分（按评委加权总分的平均值）与评委数量聚合
        if work_ids:
            agg_rows = db.query(
                WorkJudgeDimensionScore.work_id.label("work_id"),
                WorkJudgeDimensionScore.judge_id.label("judge_id"),
                func.sum(ScoreDimensions.dimension_weight * WorkJudgeDimensionScore.rate * 0.2).label("judge_total")
            ).join(ScoreDimensions, ScoreDimensions.id == WorkJudgeDimensionScore.dimension_id)
            agg_rows = agg_rows.filter(WorkJudgeDimensionScore.work_id.in_(work_ids)).group_by(WorkJudgeDimensionScore.work_id, WorkJudgeDimensionScore.judge_id).all()

            tmp = {}
            for r in agg_rows:
                tmp.setdefault(r.work_id, []).append(float(r.judge_total or 0.0))
            for wid, totals in tmp.items():
                cnt = len(totals)
                cnt_map[wid] = cnt
                avg_map[wid] = round(sum(totals) / cnt, 2) if cnt > 0 else 0.0

        # 维度级平均打分聚合（用于构建评分详情）
        dim_list = db.query(ScoreDimensions).order_by(ScoreDimensions.id.asc()).all()
        dim_avg_map = {}
        if work_ids:
            dim_rows = db.query(
                WorkJudgeDimensionScore.work_id.label("work_id"),
                WorkJudgeDimensionScore.dimension_id.label("dimension_id"),
                func.avg(WorkJudgeDimensionScore.rate).label("avg_rate")
            ).filter(WorkJudgeDimensionScore.work_id.in_(work_ids))
            dim_rows = dim_rows.group_by(WorkJudgeDimensionScore.work_id, WorkJudgeDimensionScore.dimension_id).all()
            for dr in dim_rows:
                dim_avg_map[(dr.work_id, dr.dimension_id)] = float(dr.avg_rate or 0.0)

        formatted_works = []
        for work in works:
            item = {
                "id": work.id,
                "competition_id": work.competition_id,
                "user_id": work.user_id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_file": work.work_file,
                "work_type": work.work_type,
                "work_cover": work.work_cover,
                "work_status": work.work_status,
                "work_audit_status": work.work_audit_status,
                "work_sort": work.work_sort,
                "isPinned": work.isPinned,
                "xhs_url": work.xhs_url,
                "dy_url": work.dy_url,
                "wb_url": work.wb_url,
                "sph_url": work.sph_url,
                "xhs_like": work.xhs_like,
                "dy_like": work.dy_like,
                "wb_like": work.wb_like,
                "sph_like": work.sph_like,
                "xhs_collect": work.xhs_collect,
                "dy_collect": work.dy_collect,
                "sph_collect": work.sph_collect,
                "xhs_comment": work.xhs_comment,
                "dy_comment": work.dy_comment,
                "wb_comment": work.wb_comment,
                "sph_comment": work.sph_comment,
                "wb_zf": work.wb_zf,
                "work_like": work.work_like,
                "work_comment": work.work_comment,
                "work_score": work.work_score,
                "work_rank": work.work_rank,
                "work_award": work.work_award,
                "work_views": work.work_views,
                "work_label": work.work_label,
                "wx_id": work.wx_id,
                "email": work.email,
                "release_status": work.release_status,
                "judge_count": cnt_map.get(work.id, 0),
                "avg_score": avg_map.get(work.id, 0.0),
                "create_time": work.create_time.strftime("%Y-%m-%d %H:%M:%S") if work.create_time else None,
                "update_time": work.update_time.strftime("%Y-%m-%d %H:%M:%S") if work.update_time else None,
                "delete_time": work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if work.delete_time else None
            }

            # 仅为已评分作品附加评分详情（全部维度及总分）
            if item["judge_count"] > 0:
                dimensions_detail = []
                for d in dim_list:
                    avg_rate = dim_avg_map.get((work.id, d.id), 0.0)
                    dimensions_detail.append({
                        "id": d.id,
                        "name": d.dimension_name,
                        "description": d.dimension_desc,
                        "weight": float(d.dimension_weight),
                        "avg_rate": round(avg_rate, 2),
                        "avg_score": round(float(d.dimension_weight) * avg_rate * 0.2, 2)
                    })
                item["score_detail"] = {
                    "total_score": avg_map.get(work.id, 0.0),
                    "dimensions": dimensions_detail
                }

            formatted_works.append(item)

        competition_info = {
            "id": comp.id,
            "name": comp.name,
            "description": comp.description,
            "cover": comp.cover,
            "content": comp.content,
            "type": comp.type,
            "competition_award": comp.competition_award,
            "status": comp.competition_status,
            "start_time": comp.competition_time.strftime("%Y-%m-%d %H:%M:%S") if comp.competition_time else None,
            "end_time": comp.competition_end_time.strftime("%Y-%m-%d %H:%M:%S") if comp.competition_end_time else None,
            "create_time": comp.create_time.strftime("%Y-%m-%d %H:%M:%S") if comp.create_time else None,
            "update_time": comp.update_time.strftime("%Y-%m-%d %H:%M:%S") if comp.update_time else None,
            "delete_time": comp.delete_time.strftime("%Y-%m-%d %H:%M:%S") if comp.delete_time else None
        }

        return {
            "code": 200,
            "msg": "查询成功",
            "data": {
                "competition": competition_info,
                "items": formatted_works,
                "total": total,
                "pageNum": pageNum,
                "pageSize": pageSize,
                "pages": pages
            }
        }
    except Exception as e:
        return {"code": 500, "msg": f"查询失败: {str(e)}", "data": None}


        
@router.get("/competitions/judges", summary="查询当前登录用户担任评委的比赛列表")
async def get_my_judge_competitions(include_ended: bool = False, db: Session = Depends(get_db), credentials: Optional[HTTPAuthorizationCredentials] = Depends(HTTPBearer(auto_error=False))):
    """
    查询当前登录用户作为评委的比赛列表（需登录）

    Args:
        include_ended: 是否包含已截止比赛（默认不包含）
        db: 数据库会话

    Returns:
        {code, msg, data: [{id, name}]}
    """
    if not credentials or not getattr(credentials, 'credentials', None):
        raise HTTPException(status_code=401, detail="未认证")
    uid = get_current_user_id(credentials)
    q = db.query(Competition.id, Competition.name, Competition.judge_ids).filter(Competition.competition_status == 1)
    if not include_ended:
        q = q.filter(or_(Competition.competition_end_time.is_(None), Competition.competition_end_time > datetime.now()))
    rows = q.all()
    now = datetime.now()
    data_list = []
    for cid, cname, jids in rows:
        if not jids:
            continue
        raw = str(jids).strip()
        parts = [p.strip() for p in raw.split(',') if p and p.strip()]
        ids_set = set()
        for p in parts:
            try:
                ids_set.add(int(p))
            except Exception:
                pass
        if uid not in ids_set:
            continue
        competition = db.query(Competition).filter(Competition.id == cid, Competition.delete_time.is_(None)).first()
        if not competition:
            continue
        remaining_time = None
        if competition.competition_end_time and competition.create_time:
            if competition.competition_end_time > competition.create_time:
                time_diff = competition.competition_end_time - competition.create_time
                total_seconds = int(time_diff.total_seconds())
                days = total_seconds // (24 * 3600)
                remaining_seconds = total_seconds % (24 * 3600)
                hours = remaining_seconds // 3600
                remaining_seconds = remaining_seconds % 3600
                minutes = remaining_seconds // 60
                time_parts = []
                if days > 0:
                    time_parts.append(f"{days}天")
                if hours > 0:
                    time_parts.append(f"{hours}小时")
                if minutes > 0:
                    time_parts.append(f"{minutes}分钟")
                if len(time_parts) == 0:
                    time_parts.append("不足1分钟")
                remaining_time = "".join(time_parts)
            else:
                remaining_time = "时间设置错误"
        submission_remaining_time = None
        if competition.competition_end_time:
            if competition.competition_end_time > now:
                time_diff = competition.competition_end_time - now
                total_seconds = int(time_diff.total_seconds())
                days = total_seconds // (24 * 3600)
                remaining_seconds = total_seconds % (24 * 3600)
                hours = remaining_seconds // 3600
                remaining_seconds = remaining_seconds % 3600
                minutes = remaining_seconds // 60
                time_parts = []
                if days > 0:
                    time_parts.append(f"{days}天")
                if hours > 0:
                    time_parts.append(f"{hours}小时")
                if minutes > 0:
                    time_parts.append(f"{minutes}分钟")
                if len(time_parts) == 0:
                    time_parts.append("不足1分钟")
                submission_remaining_time = "".join(time_parts)
            else:
                submission_remaining_time = "比赛已截稿"
        works_query = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == cid,
            CompetitionWork.release_status == 1,
            CompetitionWork.delete_time.is_(None)
        ).order_by(desc(CompetitionWork.create_time))
        total_works = works_query.count()
        pageNum = 1
        pageSize = 6
        total_pages = math.ceil(total_works / pageSize) if total_works > 0 else 1
        offset = (pageNum - 1) * pageSize
        competition_works = works_query.offset(offset).limit(pageSize).all()
        participant_count = db.query(func.count(func.distinct(CompetitionWork.user_id))).filter(
            CompetitionWork.competition_id == cid,
            CompetitionWork.delete_time.is_(None)
        ).scalar() or 0
        user_participated = False
        user_work = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == cid,
            CompetitionWork.user_id == uid,
            CompetitionWork.delete_time.is_(None)
        ).first()
        user_participated = user_work is not None
        award_list = []
        if competition.competition_award:
            award_parts = competition.competition_award.split(',')
            for part in award_parts:
                if ':' in part:
                    award_name = part.split(':')[0].strip()
                    award_list.append(award_name)
        works_data = []
        award_works = []
        for work in competition_works:
            author = db.query(User).filter(User.id == work.user_id).first()
            work_data = {
                "id": work.id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_file": work.work_file,
                "work_type": work.work_type,
                "work_cover": work.work_cover,
                "work_like": work.work_like,
                "work_comment": work.work_comment,
                "work_score": work.work_score,
                "work_rank": work.work_rank,
                "work_award": work.work_award,
                "work_views": work.work_views,
                "work_status": work.work_status,
                "work_audit_status": work.work_audit_status,
                "isPinned": work.isPinned,
                "create_time": work.create_time.isoformat() if work.create_time else None,
                "update_time": work.update_time.isoformat() if work.update_time else None,
                "author": {
                    "id": author.id if author else None,
                    "nickname": author.nickname if author else None,
                    "avatar": author.avatar if author else None
                } if author else None,
                "comments_top3": []
            }
            works_data.append(work_data)
            if work.work_award and work.work_award.strip() in award_list:
                award_works.append(work_data)
            try:
                top_comments = db.query(WorkComment).filter(
                    WorkComment.work_id == work.id,
                    or_(WorkComment.parent_id.is_(None), WorkComment.parent_id == 0)
                ).order_by(desc(WorkComment.create_time)).limit(3).all()
                c_user_ids = list(set([c.user_id for c in top_comments]))
                c_users = {u.id: u for u in db.query(User).filter(User.id.in_(c_user_ids)).all()} if c_user_ids else {}
                work_data["comments_top3"] = [
                    {
                        "id": c.id,
                        "content": c.content,
                        "like_count": c.like_count,
                        "create_time": c.create_time.isoformat() if c.create_time else None,
                        "user": {
                            "id": cu.id if cu else None,
                            "nickname": cu.nickname if cu else None,
                            "avatar": cu.avatar if cu else None,
                        },
                    }
                    for c in top_comments
                    for cu in [c_users.get(c.user_id)]
                ]
            except Exception:
                work_data["comments_top3"] = []
        def get_award_order(work_data):
            work_award = work_data.get("work_award", "").strip()
            try:
                return award_list.index(work_award)
            except ValueError:
                return len(award_list)
        award_works.sort(key=get_award_order)
        award_works_by_category = {}
        for award_name in award_list:
            award_works_by_category[award_name] = []
        for work_data in award_works:
            work_award = work_data.get("work_award", "").strip()
            if work_award in award_works_by_category:
                award_works_by_category[work_award].append(work_data)
        data_list.append({
            "id": competition.id,
            "name": competition.name,
            "description": competition.description,
            "cover": competition.cover,
            "content": competition.content,
            "type": competition.type,
            "competition_award": competition.competition_award,
            "status": competition.competition_status,
            "start_time": competition.competition_time.isoformat() if competition.competition_time else None,
            "end_time": competition.competition_end_time.isoformat() if competition.competition_end_time else None,
            "remaining_time": remaining_time,
            "submission_remaining_time": submission_remaining_time,
            "participant_count": participant_count,
            "user_participated": user_participated,
            "create_time": competition.create_time.isoformat() if competition.create_time else None,
            "update_time": competition.update_time.isoformat() if competition.update_time else None,
            "works": works_data,
            "works_count": len(works_data),
            "works_total": total_works,
            "works_page_num": pageNum,
            "works_page_size": pageSize,
            "works_total_pages": total_pages,
            "award_works": award_works,
            "award_works_by_category": award_works_by_category,
            "award_works_count": len(award_works)
        })
    return {"code": 200, "message": "获取成功", "data": data_list}

@router.get("/competitions", summary="获取比赛列表")
async def get_competitions(
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(10, ge=1, le=100, description="每页数量"),
    name: Optional[str] = Query(None, description="比赛名称搜索"),
    type: Optional[str] = Query(None, description="比赛类型筛选"),
    status: Optional[int] = Query(None, description="比赛状态筛选"),
    db: Session = Depends(get_db),
    # credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    获取比赛列表
    支持分页、搜索、筛选和排序功能
    """
    try:
        # 构建查询条件
        query = db.query(Competition).filter(Competition.delete_time.is_(None))
        
        # 名称搜索
        if name:
            query = query.filter(Competition.name.contains(name))
        
        # 类型筛选
        if type:
            query = query.filter(Competition.type == type)
        
        # 状态筛选
        if status is not None:
            query = query.filter(Competition.competition_status == status)
   
        # 获取总数
        total = query.count()
        
        # 分页
        offset = (pageNum - 1) * pageSize
        competitions = query.offset(offset).limit(pageSize).all()
        
        # 计算总页数
        pages = math.ceil(total / pageSize) if total > 0 else 1
        
        # 格式化datetime字段为字符串
        formatted_competitions = []
        for comp in competitions:
            comp_dict = {
                "id": comp.id,
                "name": comp.name,
                "type": comp.type,
                "competition_rule": comp.competition_rule,
                "description": comp.description,
                "content": comp.content,
                "cover": comp.cover,
                "competition_number": comp.competition_number,
                "competition_time": comp.competition_time.strftime("%Y-%m-%d %H:%M:%S") if comp.competition_time else None,
                "competition_end_time": comp.competition_end_time.strftime("%Y-%m-%d %H:%M:%S") if comp.competition_end_time else None,
                "judge_number": comp.judge_number,
                "judge_ids": comp.judge_ids,
                "competition_status": comp.competition_status,
                "competition_award": comp.competition_award,
                "create_time": comp.create_time.strftime("%Y-%m-%d %H:%M:%S") if comp.create_time else None,
                "update_time": comp.update_time.strftime("%Y-%m-%d %H:%M:%S") if comp.update_time else None,
                "delete_time": comp.delete_time.strftime("%Y-%m-%d %H:%M:%S") if comp.delete_time else None
            }
            formatted_competitions.append(comp_dict)
        
        return {
            "code": 200,
            "msg": "获取比赛列表成功",
            "data": {
                "items": formatted_competitions,
                "total": total,
                "pageNum": pageNum,
                "pageSize": pageSize,
                "pages": pages
            }
        }
    
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取比赛列表失败: {str(e)}",
            "data": None
        }


@router.get("/competitions/{competition_id}", summary="获取比赛详情")
async def get_competition(
    competition_id: int, 
    search: Optional[str] = Query(None, description="作品搜索（支持作者名或作品名）"),
    type: Optional[str] = Query(None, description="new/hot"),
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(6, ge=1, le=100, description="每页数量"),
    db: Session = Depends(get_db),
    credentials: Optional[HTTPAuthorizationCredentials] = Depends(HTTPBearer(auto_error=False))
):
    """
    根据ID获取比赛详情，包含该比赛的所有参赛作品
    """
    try:
        # 获取当前时间
        now = datetime.now()
        
        # 获取当前用户ID（如果已登录；未登录不抛错，保持公开访问）
        user_id = None
        if credentials and getattr(credentials, "credentials", None):
            try:
                payload = verify_token(credentials.credentials)
                sub = payload.get("sub")
                user_id = int(sub) if sub is not None else None
            except Exception:
                user_id = None
        
        competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not competition:
            raise HTTPException(status_code=404, detail="比赛不存在")
        
        # 计算比赛剩余时间（天、小时、分钟格式）
        remaining_time = None
        if competition.competition_end_time and competition.create_time:
            # 计算比赛总时长：结束时间减去创建时间
            if competition.competition_end_time > competition.create_time:
                # 计算时间差
                time_diff = competition.competition_end_time - competition.create_time
                total_seconds = int(time_diff.total_seconds())
                
                # 转换为天、小时、分钟格式
                days = total_seconds // (24 * 3600)
                remaining_seconds = total_seconds % (24 * 3600)
                
                hours = remaining_seconds // 3600
                remaining_seconds = remaining_seconds % 3600
                
                minutes = remaining_seconds // 60
                
                # 构建时间字符串
                time_parts = []
                if days > 0:
                    time_parts.append(f"{days}天")
                if hours > 0:
                    time_parts.append(f"{hours}小时")
                if minutes > 0:
                    time_parts.append(f"{minutes}分钟")
                if len(time_parts) == 0:
                    time_parts.append("不足1分钟")
                
                remaining_time = "".join(time_parts)
            else:
                remaining_time = "时间设置错误"  # 结束时间早于创建时间
        
        # 计算剩余截稿时间（天、小时、分钟格式）
        submission_remaining_time = None
        if competition.competition_end_time:
            # 计算当前时间到比赛结束时间的剩余时间
            if competition.competition_end_time > now:
                # 计算时间差
                time_diff = competition.competition_end_time - now
                total_seconds = int(time_diff.total_seconds())
                
                # 转换为天、小时、分钟格式
                days = total_seconds // (24 * 3600)
                remaining_seconds = total_seconds % (24 * 3600)
                
                hours = remaining_seconds // 3600
                remaining_seconds = remaining_seconds % 3600
                
                minutes = remaining_seconds // 60
                
                # 构建时间字符串
                time_parts = []
                if days > 0:
                    time_parts.append(f"{days}天")
                if hours > 0:
                    time_parts.append(f"{hours}小时")
                if minutes > 0:
                    time_parts.append(f"{minutes}分钟")
                if len(time_parts) == 0:
                    time_parts.append("不足1分钟")
                
                submission_remaining_time = "".join(time_parts)
            else:
                submission_remaining_time = "比赛已截稿"  # 比赛已结束
        
        # 查询该比赛的所有参赛作品（release_status = 1）
        works_query = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.release_status == 1,
            CompetitionWork.delete_time.is_(None)
        )
        
        # 如果有搜索参数，添加搜索条件
        if search:
            # 联表查询用户信息，支持按作者名或作品名搜索
            # 注意：这里的联表查询不会影响原有的比赛ID限制条件
            works_query = works_query.join(User, CompetitionWork.user_id == User.id).filter(
                or_(
                    CompetitionWork.work_name.contains(search),  # 作品名模糊搜索
                    User.nickname.contains(search)  # 作者昵称模糊搜索
                )
            )
        
        # 根据type参数进行排序
        if type == "new":
            # 按创建时间降序排序（最新的在前面）
            works_query = works_query.order_by(desc(CompetitionWork.create_time))
        elif type == "hot":
            # 按热门度排序：浏览量 + 点赞量，热门的在前面
            works_query = works_query.order_by(desc(CompetitionWork.work_views + CompetitionWork.work_like))
        else:
            # 默认排序：按创建时间降序
            works_query = works_query.order_by(desc(CompetitionWork.create_time))
        
        # 获取作品总数（用于分页计算）
        total_works = works_query.count()
        
        # 计算分页参数
        total_pages = math.ceil(total_works / pageSize) if total_works > 0 else 1
        offset = (pageNum - 1) * pageSize
        
        # 应用分页查询
        competition_works = works_query.offset(offset).limit(pageSize).all()
        
        # 统计比赛参与人数（去重用户ID）
        participant_count = db.query(func.count(func.distinct(CompetitionWork.user_id))).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None)
        ).scalar() or 0
        
        # 检查当前用户是否参与了该比赛
        user_participated = False
        if user_id: 
            user_work = db.query(CompetitionWork).filter(
                CompetitionWork.competition_id == competition_id,
                CompetitionWork.user_id == user_id,
                CompetitionWork.delete_time.is_(None)
            ).first()
            user_participated = user_work is not None
        
        # 解析比赛奖项设置
        award_list = []
        if competition.competition_award:
            # 解析奖项字符串，格式：一等奖:500积分,二等奖:400积分,三等奖:300积分,入围奖:200积分,优秀奖:100积分
            award_parts = competition.competition_award.split(',')
            for part in award_parts:
                if ':' in part:
                    award_name = part.split(':')[0].strip()
                    award_list.append(award_name)
        
        # 构建作品列表数据和获奖作品数据
        works_data = []
        award_works = []  # 获奖作品公示数组
        
        for work in competition_works:
            # 查询作品作者信息
            author = db.query(User).filter(User.id == work.user_id).first()
            
            work_data = {
                "id": work.id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_file": work.work_file,
                "work_type": work.work_type,
                "work_cover": work.work_cover,
                "work_like": work.work_like,
                "work_comment": work.work_comment,
                "work_score": work.work_score,
                "work_rank": work.work_rank,
                "work_award": work.work_award,
                "work_views": work.work_views,
                "work_status": work.work_status,
                "work_audit_status": work.work_audit_status,
                "isPinned": work.isPinned,
                "create_time": work.create_time.isoformat() if work.create_time else None,
                "update_time": work.update_time.isoformat() if work.update_time else None,
                "author": {
                    "id": author.id if author else None,
                    "nickname": author.nickname if author else None,
                    "avatar": author.avatar if author else None
                } if author else None,
                "comments_top3": []
            }

            works_data.append(work_data)
            
            # 检查作品是否获奖，如果获奖且奖项在比赛设置的奖项中，则添加到获奖公示数组
            if work.work_award and work.work_award.strip() in award_list:
                award_works.append(work_data)

            # 附加：为未登录用户提供每个作品的前三条顶级评论（按时间倒序）
            try:
                top_comments = db.query(WorkComment).filter(
                    WorkComment.work_id == work.id,
                    or_(WorkComment.parent_id.is_(None), WorkComment.parent_id == 0)
                ).order_by(desc(WorkComment.create_time)).limit(3).all()
                # 批量查询评论作者信息
                c_user_ids = list(set([c.user_id for c in top_comments]))
                c_users = {u.id: u for u in db.query(User).filter(User.id.in_(c_user_ids)).all()} if c_user_ids else {}
                work_data["comments_top3"] = [
                    {
                        "id": c.id,
                        "content": c.content,
                        "like_count": c.like_count,
                        "create_time": c.create_time.isoformat() if c.create_time else None,
                        "user": {
                            "id": cu.id if cu else None,
                            "nickname": cu.nickname if cu else None,
                            "avatar": cu.avatar if cu else None,
                        },
                    }
                    for c in top_comments
                    for cu in [c_users.get(c.user_id)]
                ]
            except Exception:
                work_data["comments_top3"] = []
        
        # 按照比赛奖项设置的顺序对获奖作品进行排序
        def get_award_order(work_data):
            """
            获取奖项在比赛设置中的顺序索引
            返回值越小，排序越靠前
            """
            work_award = work_data.get("work_award", "").strip()
            try:
                return award_list.index(work_award)
            except ValueError:
                # 如果奖项不在列表中，排到最后
                return len(award_list)
        
        # 对获奖作品按奖项顺序排序
        award_works.sort(key=get_award_order)
        
        # 按奖项分类获奖作品
        award_works_by_category = {}
        for award_name in award_list:
            award_works_by_category[award_name] = []
        
        # 将获奖作品按奖项分类
        for work_data in award_works:
            work_award = work_data.get("work_award", "").strip()
            if work_award in award_works_by_category:
                award_works_by_category[work_award].append(work_data)
            
        return {
            "code": 200,
            "message": "获取成功",
            "data": {
                "id": competition.id,
                "name": competition.name,
                "description": competition.description,
                "cover": competition.cover,
                "content": competition.content,
                "type": competition.type,
                "competition_award": competition.competition_award,
                "status": competition.competition_status,
                "start_time": competition.competition_time.isoformat() if competition.competition_time else None,
                "end_time": competition.competition_end_time.isoformat() if competition.competition_end_time else None,
                "remaining_time": remaining_time,  # 作品征集时间
                "submission_remaining_time": submission_remaining_time,  # 剩余截稿时间（天、小时、分钟格式）
                "participant_count": participant_count,  # 比赛参与人数
                "user_participated": user_participated,  # 当前用户是否参与
                "create_time": competition.create_time.isoformat() if competition.create_time else None,
                "update_time": competition.update_time.isoformat() if competition.update_time else None,
                "works": works_data,
                "works_count": len(works_data),
                "works_total": total_works,  # 作品总数
                "works_page_num": pageNum,  # 当前页码
                "works_page_size": pageSize,  # 每页数量
                "works_total_pages": total_pages,  # 总页数
                "award_works": award_works,  # 获奖作品公示数组（按奖项顺序排序）
                "award_works_by_category": award_works_by_category,  # 获奖作品按奖项分类
                "award_works_count": len(award_works)  # 获奖作品数量
            }
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取比赛详情失败: {str(e)}")


# 通知相关接口
@router.get("/notifications", summary="获取用户通知列表")
async def get_user_notifications(
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(10, ge=1, le=100, description="每页数量"),
    category: Optional[str] = Query(None, description="通知分类筛选：all-全部，follow-关注，system-系统，notice-公告"),
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    获取用户通知列表，按照通知类型分类返回
    
    通知分类说明：
    - all: 全部通知（包括系统、公告、关注等所有类型）
    - system: 系统通知（notification_type=1）
    - notice: 公告通知（notification_type=2）
    - follow: 关注相关通知（notification_type=3）
    
    返回数据结构：按类型分组，已读和未读通知在同一列表中，未读通知排在前面
    
    数据库索引建议：
    - 复合索引：(user_id, is_delete, expire_time)
    - 单列索引：notification_type, is_read, create_time
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        logger.info(f"用户 {current_user_id} 请求通知列表，分类: {category}")
        
        # 构建优化的基础查询条件
        # 处理个人通知和全体用户通知（公告类通知）
        from sqlalchemy import or_, and_, func, text
        import json
        from datetime import datetime
        
        # 查询条件：user_id字段包含当前用户ID（支持JSON格式的用户ID列表）
        base_query = db.query(UserNotification).filter(
            or_(
                # 单个用户通知：user_id直接等于当前用户ID字符串
                UserNotification.user_id == str(current_user_id),
                # 群发通知：user_id为JSON数组，包含当前用户ID（MySQL JSON_CONTAINS，需要传入 JSON 字符串）
                func.json_contains(UserNotification.user_id, json.dumps(str(current_user_id)))
            ),
            # 排除已删除的通知：检查当前用户ID不在is_delete_id列表中
            or_(
                UserNotification.is_delete_id.is_(None),
                ~func.json_contains(UserNotification.is_delete_id, json.dumps(str(current_user_id)))
            )
        )
        
        # 定义通知类型映射
        type_mapping = {
            'system': [1],  # 系统消息
            'notice': [2],  # 公告通知
            'follow': [3],  # 关注消息
            'all': [1, 2, 3]  # 全部类型
        }
        
        # 根据分类筛选通知类型
        if category and category in type_mapping:
            notification_types = type_mapping[category]
            base_query = base_query.filter(UserNotification.notification_type.in_(notification_types))
        elif category and category not in type_mapping:
            # 无效的分类参数
            return {
                "code": 400,
                "msg": "无效的通知分类参数，支持的分类：all、system、notice、follow",
                "data": None
            }
        
        # 获取所有通知并按类型分组，未读通知排在前面
        # 根据is_read_id字段判断是否已读：如果is_read_id为None或不包含当前用户ID则为未读
        all_notifications = base_query.order_by(
            desc(UserNotification.create_time)
        ).all()
        
        # 手动排序：未读通知在前

        def is_notification_read(notification):
            """
            判断通知是否已被当前用户读取
            
            Args:
                notification: 通知记录对象
            Returns:
                bool: 当前用户是否已读
            """
            read_ids = safe_parse_json_list(notification.is_read_id)
            return str(current_user_id) in read_ids
        
        # 按已读状态和创建时间排序
        def sort_key(x):
            read_flag = is_notification_read(x)
            ts = 0.0
            try:
                ts = (x.create_time.timestamp() if x.create_time else 0.0)
            except Exception:
                ts = 0.0
            return (read_flag, -ts)

        all_notifications.sort(key=sort_key)
        
        def format_notification_data(notification):
            """
            格式化单个通知数据
            
            Args:
                notification: 通知记录对象
            Returns:
                dict: 序列化后的通知字典
            """
            return {
                "id": notification.id,
                "type": notification.notification_type,
                "title": notification.title,
                "content": notification.content,
                "related_id": notification.related_id,
                "related_type": notification.related_type,
                "is_read": is_notification_read(notification),  # 基于is_read_id字段判断当前用户是否已读
                "create_time": notification.create_time.strftime("%Y-%m-%d %H:%M:%S") if notification.create_time else None,
                "read_time": None  # 由于模型中没有read_time字段，设为None
            }
        
        def get_category_data(notifications, category_types):
            """
            获取指定类型的通知数据，已读和未读通知在同一列表中
            
            Args:
                notifications: 通知列表
                category_types: 类型过滤列表
            Returns:
                dict: 分页后的分类数据
            """
            # 筛选指定类型的通知
            filtered_notifications = [n for n in notifications if n.notification_type in category_types]
            
            # 计算总数和分页
            total = len(filtered_notifications)
            offset = (pageNum - 1) * pageSize
            page_notifications = filtered_notifications[offset:offset + pageSize]
            
            return {
                "list": [format_notification_data(n) for n in page_notifications],
                "total": total,
                "pageNum": pageNum,
                "pageSize": pageSize,
                "totalPages": math.ceil(total / pageSize) if total > 0 else 0
            }
        
        # 构建返回数据结构
        result_data = {}
        
        if category:
            # 返回指定分类的数据
            if category in type_mapping:
                result_data[category] = get_category_data(all_notifications, type_mapping[category])
        else:
            # 返回所有分类的数据
            result_data["all"] = get_category_data(all_notifications, type_mapping["all"])
            result_data["system"] = get_category_data(all_notifications, type_mapping["system"])
            result_data["notice"] = get_category_data(all_notifications, type_mapping["notice"])
            result_data["follow"] = get_category_data(all_notifications, type_mapping["follow"])
        
        return {
            "code": 200,
            "msg": "获取通知列表成功",
            "data": result_data
        }
        
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取通知列表失败: {str(e)}",
            "data": None
        }


@router.put("/notifications/read", summary="批量标记通知为已读")
async def mark_notifications_read(
    notification_ids: str = Query(..., description="通知ID列表，逗号分隔"),
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    批量标记通知为已读
    
    支持个人通知和群发通知的标记：
    - 个人通知：user_id字段直接等于当前用户ID字符串
    - 群发通知：user_id字段为JSON格式的用户ID列表，使用JSON_CONTAINS查询
    
    Args:
        notification_ids: 通知ID列表，用逗号分隔的字符串
        db: 数据库会话
        credentials: 用户认证凭据
    
    Returns:
        dict: 包含操作结果的响应数据
    
    Note:
        - 使用is_read_id字段存储已读用户ID列表（JSON格式）
        - 如果用户已读过该通知，不会重复添加
        - 支持单个或多个通知ID，用逗号分隔
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 解析通知ID列表
        try:
            id_list = [int(id_str.strip()) for id_str in notification_ids.split(',') if id_str.strip()]
        except ValueError:
            return {
                "code": 400,
                "msg": "通知ID格式错误，请提供有效的数字ID",
                "data": None
            }
        
        if not id_list:
            return {
                "code": 400,
                "msg": "请提供至少一个通知ID",
                "data": None
            }
        
        # 查找通知（支持个人通知和群发通知）
        notifications = db.query(UserNotification).filter(
            UserNotification.id.in_(id_list),
            or_(
                # 单个用户通知：user_id直接等于当前用户ID字符串
                UserNotification.user_id == str(current_user_id),
                # 群发通知：user_id为JSON数组，包含当前用户ID
                text(f"JSON_CONTAINS(user_id, '{current_user_id}')")
            ),
            # 排除已删除的通知：检查当前用户ID不在is_delete_id列表中
            or_(
                UserNotification.is_delete_id.is_(None),  # is_delete_id为空
                ~text(f"JSON_CONTAINS(is_delete_id, '{current_user_id}')")  # 或当前用户ID不在删除列表中
            )
        ).all()
        
        updated_count = 0
        # 批量更新已读状态
        for notification in notifications:
            # 处理is_read_id字段：将当前用户ID添加到已读用户列表
            read_ids = safe_parse_json_list(notification.is_read_id)
            if str(current_user_id) not in read_ids:
                read_ids.append(str(current_user_id))
                notification.is_read_id = json.dumps(read_ids)
                updated_count += 1
        
        db.commit()
        
        return {
            "code": 200,
            "msg": f"成功标记 {updated_count} 条通知为已读",
            "data": {
                "updated_count": updated_count,
                "notification_ids": id_list
            }
        }
        
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"批量标记已读失败: {str(e)}",
            "data": None
        }




@router.delete("/notifications/{notification_id}", summary="删除通知")
async def delete_notification(
    notification_id: int,
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    删除通知（软删除）
    
    支持个人通知和群发通知的删除：
    - 个人通知：user_id字段直接等于当前用户ID字符串
    - 群发通知：user_id字段为JSON格式的用户ID列表，使用JSON_CONTAINS查询
    
    Args:
        notification_id: 要删除的通知ID
        db: 数据库会话
        credentials: 用户认证凭据
    
    Returns:
        dict: 包含操作结果的响应数据
    
    Note:
        - 使用软删除，将当前用户ID添加到is_delete_id字段的JSON列表中
        - 只能删除属于当前用户的通知
        - 已删除的通知不会在列表中显示
        - 支持多用户对同一通知的独立删除状态管理
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 查找通知（支持个人通知和群发通知）
        notification = db.query(UserNotification).filter(
            UserNotification.id == notification_id,
            or_(
                # 单个用户通知：user_id直接等于当前用户ID字符串
                UserNotification.user_id == str(current_user_id),
                # 群发通知：user_id为JSON数组，包含当前用户ID
                text(f"JSON_CONTAINS(user_id, '{current_user_id}')")
            ),
            # 排除已删除的通知：检查当前用户ID不在is_delete_id列表中
            or_(
                UserNotification.is_delete_id.is_(None),  # is_delete_id为空
                ~text(f"JSON_CONTAINS(is_delete_id, '{current_user_id}')")  # 或当前用户ID不在删除列表中
            )
        ).first()
        
        if not notification:
            return {
                "code": 404,
                "msg": "通知不存在",
                "data": None
            }
        
        # 软删除：将当前用户ID添加到is_delete_id列表中
        import json
        delete_ids = safe_parse_json_list(notification.is_delete_id)
        if str(current_user_id) not in delete_ids:
            delete_ids.append(str(current_user_id))
            notification.is_delete_id = json.dumps(delete_ids)
        
        db.commit()
        
        return {
            "code": 200,
            "msg": "删除通知成功",
            "data": {
                "id": notification.id
            }
        }
        
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"删除通知失败: {str(e)}",
            "data": None
        }

@router.post("/competitions", summary="创建比赛")
async def create_competition(competition: CompetitionCreate, db: Session = Depends(get_db)):
    """
    创建新比赛并发送系统通知给所有用户
    
    创建比赛后会自动生成一条系统通知：
    - 通知类型：系统通知（notification_type=1）
    - 接收者：所有用户（user_id存储为JSON格式的用户ID列表）
    - 初始状态：所有用户均为未读（is_read_id=None）
    
    Args:
        competition: 比赛创建数据
        db: 数据库会话
    
    Returns:
        dict: 包含创建的比赛信息的响应数据
    
    Note:
        - 创建比赛成功后会自动发送通知给所有用户
        - 通知的user_id字段存储所有用户ID的JSON列表
        - 使用事务确保比赛和通知都创建成功
    """
    try:
        # 创建比赛对象
        db_competition = Competition(**competition.dict())
        
        # 保存到数据库
        db.add(db_competition)
        db.commit()
        db.refresh(db_competition)
        
        # 创建系统通知给所有用户（新比赛发布）
        import json
        
        # 获取所有用户ID列表
        all_users = db.query(User).all()
        user_ids = [str(user.id) for user in all_users]
        
        # 创建一条通知记录，user_id字段存储所有用户ID的JSON列表
        notification = UserNotification(
            user_id=json.dumps(user_ids),  # 存储用户ID列表的JSON格式
            sender_id=None,  # 系统消息，发送者为空
            notification_type=1,  # 1-系统通知
            title='新比赛发布',
            content=f'新比赛《{db_competition.name}》已发布，快来参加吧！',
            related_id=db_competition.id,
            related_type='competition',
            is_read_id=None,  # 初始时没有用户已读
            is_delete_id=None  # 初始时没有用户删除该通知
        )
        db.add(notification)
        db.commit()  # 提交通知
        
        # 格式化datetime字段为字符串
        competition_dict = {
            "id": db_competition.id,
            "name": db_competition.name,
            "type": db_competition.type,
            "competition_rule": db_competition.competition_rule,
            "description": db_competition.description,
            "content": db_competition.content,
            "cover": db_competition.cover,
            "competition_number": db_competition.competition_number,
            "competition_time": db_competition.competition_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.competition_time else None,
            "competition_end_time": db_competition.competition_end_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.competition_end_time else None,
            "judge_number": db_competition.judge_number,
            "judge_ids": db_competition.judge_ids,
            "competition_status": db_competition.competition_status,
            "competition_award": db_competition.competition_award,
            "create_time": db_competition.create_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.create_time else None,
            "update_time": db_competition.update_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.update_time else None,
            "delete_time": db_competition.delete_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.delete_time else None
        }
        
        return {
            "code": 200,
            "msg": "创建比赛成功",
            "data": competition_dict
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"创建比赛失败: {str(e)}",
            "data": None
        }


@router.put("/competitions/{competition_id}", summary="更新比赛")
async def update_competition(
    competition_id: int, 
    competition: CompetitionUpdate, 
    db: Session = Depends(get_db)
):
    """
    更新比赛信息
    """
    try:
        # 查找比赛
        db_competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not db_competition:
            return {
                "code": 404,
                "msg": "比赛不存在",
                "data": None
            }
        
        # 更新字段
        update_data = competition.dict(exclude_unset=True)
        for field, value in update_data.items():
            if field == 'cover':
                safe_replace_file(db_competition, 'cover', value)
            else:
                setattr(db_competition, field, value)
        
        db_competition.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        db.refresh(db_competition)
        
        # 格式化datetime字段为字符串
        competition_dict = {
            "id": db_competition.id,
            "name": db_competition.name,
            "type": db_competition.type,
            "competition_rule": db_competition.competition_rule,
            "description": db_competition.description,
            "content": db_competition.content,
            "cover": db_competition.cover,
            "competition_number": db_competition.competition_number,
            "competition_time": db_competition.competition_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.competition_time else None,
            "competition_end_time": db_competition.competition_end_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.competition_end_time else None,
            "judge_number": db_competition.judge_number,
            "judge_ids": db_competition.judge_ids,
            "competition_status": db_competition.competition_status,
            "competition_award": db_competition.competition_award,
            "create_time": db_competition.create_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.create_time else None,
            "update_time": db_competition.update_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.update_time else None,
            "delete_time": db_competition.delete_time.strftime("%Y-%m-%d %H:%M:%S") if db_competition.delete_time else None
        }
        
        return {
            "code": 200,
            "msg": "更新比赛成功",
            "data": competition_dict
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"更新比赛失败: {str(e)}",
            "data": None
        }


@router.delete("/competitions/{competition_id}", summary="删除比赛")
async def delete_competition(competition_id: int, db: Session = Depends(get_db)):
    """
    软删除比赛（设置删除时间）
    """
    try:
        # 查找比赛
        db_competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not db_competition:
            return {
                "code": 404,
                "msg": "比赛不存在",
                "data": None
            }
        
        # 软删除
        db_competition.delete_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "比赛删除成功",
            "data": None
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"删除比赛失败: {str(e)}",
            "data": None
        }


@router.put("/competitions/{competition_id}/status", summary="更新比赛状态")
async def update_competition_status(
    competition_id: int,
    status: int,
    db: Session = Depends(get_db)
):
    """
    更新比赛状态
    """
    try:
        # 查找比赛
        db_competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not db_competition:
            return {
                "code": 404,
                "msg": "比赛不存在",
                "data": None
            }
        
        # 更新状态
        db_competition.competition_status = status
        db_competition.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "比赛状态更新成功",
            "data": None
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"更新比赛状态失败: {str(e)}",
            "data": None
        }


# ========== 参赛作品管理接口 ==========

@router.get("/works", summary="获取参赛作品列表")
async def get_competition_works(
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(10, ge=1, le=100, description="每页数量"),
    competition_id: Optional[int] = Query(None, description="比赛ID筛选"),
    user_id: Optional[int] = Query(None, description="用户ID筛选"),
    work_name: Optional[str] = Query(None, description="作品名称搜索"),
    work_type: Optional[str] = Query(None, description="作品类型筛选"),
    work_status: Optional[int] = Query(None, description="作品状态筛选"),
    work_audit_status: Optional[int] = Query(None, description="审核状态筛选"),
    release_status: Optional[int] = Query(None, description="发布状态筛选 1-作品 2-草稿"),
    db: Session = Depends(get_db)
):
    """
    获取参赛作品列表
    支持分页、搜索、筛选和排序功能
    """
    try:
        # 构建查询条件
        query = db.query(CompetitionWork).filter(CompetitionWork.delete_time.is_(None))
        
        # 比赛ID筛选
        if competition_id:
            query = query.filter(CompetitionWork.competition_id == competition_id)
        
        # 用户ID筛选
        if user_id:
            query = query.filter(CompetitionWork.user_id == user_id)
        
        # 作品名称搜索
        if work_name:
            query = query.filter(CompetitionWork.work_name.contains(work_name))
        
        # 作品类型筛选
        if work_type:
            query = query.filter(CompetitionWork.work_type == work_type)
        
        # 作品状态筛选
        if work_status is not None:
            query = query.filter(CompetitionWork.work_status == work_status)
        
        # 审核状态筛选
        if work_audit_status is not None:
            query = query.filter(CompetitionWork.work_audit_status == work_audit_status)
        
        # 发布状态筛选
        if release_status is not None:
            query = query.filter(CompetitionWork.release_status == release_status)
        
        # 获取总数
        total = query.count()
        
        # 排序：work_sort值越大越靠前（置顶），相同时按创建时间倒序
        query = query.order_by(desc(CompetitionWork.work_sort), desc(CompetitionWork.create_time))
        
        # 分页
        offset = (pageNum - 1) * pageSize
        works = query.offset(offset).limit(pageSize).all()
        
        # 计算总页数
        pages = math.ceil(total / pageSize) if total > 0 else 1
        
        # 格式化datetime字段为字符串
        formatted_works = []
        for work in works:
            work_dict = {
                "id": work.id,
                "competition_id": work.competition_id,
                "user_id": work.user_id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_file": work.work_file,
                "work_type": work.work_type,
                "work_cover": work.work_cover,
                "work_status": work.work_status,
                "work_audit_status": work.work_audit_status,
                "work_sort": work.work_sort,
                "isPinned": work.isPinned,  # 添加置顶状态字段
                "xhs_url": work.xhs_url,
                "dy_url": work.dy_url,
                "wb_url": work.wb_url,
                "sph_url": work.sph_url,
                "xhs_like": work.xhs_like,
                "dy_like": work.dy_like,
                "wb_like": work.wb_like,
                "sph_like": work.sph_like,
                "xhs_collect": work.xhs_collect,
                "dy_collect": work.dy_collect,
                "sph_collect": work.sph_collect,
                "xhs_comment": work.xhs_comment,
                "dy_comment": work.dy_comment,
                "wb_comment": work.wb_comment,
                "sph_comment": work.sph_comment,
                "wb_zf": work.wb_zf,

                "work_like": work.work_like,
                "work_comment": work.work_comment,
                "work_score": work.work_score,
                "work_rank": work.work_rank,
                "work_award": work.work_award,
                "work_views": work.work_views,  # 作品浏览量
                "work_label": work.work_label,  # 作品标签
                "wx_id": work.wx_id,
                "email": work.email,
                "release_status": work.release_status,
                "create_time": work.create_time.strftime("%Y-%m-%d %H:%M:%S") if work.create_time else None,
                "update_time": work.update_time.strftime("%Y-%m-%d %H:%M:%S") if work.update_time else None,
                "delete_time": work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if work.delete_time else None
            }
            formatted_works.append(work_dict)
        
        return {
            "code": 200,
            "msg": "获取作品列表成功",
            "data": {
                "items": formatted_works,
                "total": total,
                "pageNum": pageNum,
                "pageSize": pageSize,
                "pages": pages
            }
        }
    
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取作品列表失败: {str(e)}",
            "data": None
        }


@router.get("/works/{work_id}", summary="获取作品详情")
async def get_competition_work(
    work_id: int, 
    db: Session = Depends(get_db),
    credentials: Optional[HTTPAuthorizationCredentials] = Depends(security)
):
    """
    根据ID获取作品详情，并增加浏览量
    - 支持JWT认证，获取当前用户对该作品的点赞状态
    - 如果用户未登录，is_liked字段为False
    """
    try:
        # 从token中获取当前用户ID（可选）
        current_user_id = None
        if credentials:
            try:
                current_user_id = get_current_user_id(credentials)
            except:
                # 如果token无效，忽略错误，继续执行（游客模式）
                pass
        
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 增加浏览量 - 使用原生SQL避免触发update_time自动更新
        db.execute(
            text("UPDATE competition_work SET work_views = COALESCE(work_views, 0) + 1 WHERE id = :work_id"),
            {"work_id": work_id}
        )
        db.commit()
        
        # 重新查询作品以获取最新的浏览量
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        # 查询作者信息
        author = db.query(User).filter(User.id == work.user_id).first()
        
        # 判断当前用户是否关注作者
        is_following = False
        if current_user_id and author:
            follow_relation = db.query(UserFollow).filter(
                UserFollow.follower_id == current_user_id,
                UserFollow.following_id == author.id,
                UserFollow.is_delete == False
            ).first()
            is_following = follow_relation is not None
        
        # 查询当前用户对该作品的点赞状态
        is_liked = False
        if current_user_id:
            work_like = db.query(WorkLike).filter(
                WorkLike.work_id == work_id,
                WorkLike.user_id == current_user_id,
                WorkLike.is_like == True
            ).first()
            is_liked = work_like is not None
        
        # 格式化datetime字段为字符串
        work_dict = {
            "id": work.id,
            "competition_id": work.competition_id,
            "user_id": work.user_id,
            "work_name": work.work_name,
            "work_description": work.work_description,
            "work_file": work.work_file,
            "work_type": work.work_type,
            "work_cover": work.work_cover,
            "work_status": work.work_status,
            "work_audit_status": work.work_audit_status,
            "work_sort": work.work_sort,
            "xhs_url": work.xhs_url,
            "dy_url": work.dy_url,
            "wb_url": work.wb_url,
            "sph_url": work.sph_url,
            "xhs_like": work.xhs_like,
            "dy_like": work.dy_like,
            "wb_like": work.wb_like,
            "sph_like": work.sph_like,
            "xhs_collect": work.xhs_collect,
            "dy_collect": work.dy_collect,
            "sph_collect": work.sph_collect,
            "xhs_comment": work.xhs_comment,
            "dy_comment": work.dy_comment,
            "wb_comment": work.wb_comment,
            "sph_comment": work.sph_comment,
            "wb_zf": work.wb_zf,
            "work_like": work.work_like,
            "work_comment": work.work_comment,
            "work_score": work.work_score,
            "work_rank": work.work_rank,
            "work_award": work.work_award,
            "work_views": work.work_views,  # 作品浏览量
            "work_label": work.work_label,  # 作品标签
            "wx_id": work.wx_id,
            "email": work.email,
            "release_status": work.release_status,
            "is_liked": is_liked,  # 当前用户是否已点赞该作品
            "create_time": work.create_time.strftime("%Y-%m-%d %H:%M:%S") if work.create_time else None,
            "update_time": work.update_time.strftime("%Y-%m-%d %H:%M:%S") if work.update_time else None,
            "delete_time": work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if work.delete_time else None,
            # 作者信息
            "author": {
                "id": author.id if author else None,
                "province": author.province if author else None,
                "city": author.city if author else None,
                "nickname": author.nickname if author else None,
                "avatar": author.avatar if author else None,
                "description": author.description if author else None,
                "like_num": author.like_num if author else 0,
                "comment_num": author.comment_num if author else 0,
                "follow_num": author.follow_num if author else 0,
                "fans_num": author.fans_num if author else 0,
                "is_following": is_following  # 当前用户是否关注作者
            }
        }
        
        return {
            "code": 200,
            "msg": "获取作品详情成功",
            "data": work_dict
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"获取作品详情失败: {str(e)}",
            "data": None
        }


@router.post("/works", summary="创建参赛作品")
async def create_competition_work(
    work: CompetitionWorkCreate, 
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    创建新的参赛作品
    注意：user_id不需要在请求体中传递，会通过JWT token自动获取
    
    Args:
        work: 作品创建数据（不包含user_id）
        db: 数据库会话
        credentials: JWT认证凭据
    
    Returns:
        创建成功的作品信息
    """
    try:
        # 从JWT token中获取用户ID
        user_id = get_current_user_id(credentials)
        
        # 验证发布状态和比赛ID的关系
        release_status = work.release_status or 2  # 默认为草稿状态
        
        if release_status == 1:  # 发布作品
            if not work.competition_id:
                return {
                    "code": 400,
                    "msg": "提交作品时必须选择比赛",
                    "data": None
                }
            
            # 验证比赛是否存在
            competition = db.query(Competition).filter(
                Competition.id == work.competition_id,
                Competition.delete_time.is_(None)
            ).first()
            
            if not competition:
                return {
                    "code": 404,
                    "msg": "比赛不存在",
                    "data": None
                }
            
            # 检查用户是否已经参加过该比赛（只检查非草稿状态的作品）
            existing_work = db.query(CompetitionWork).filter(
                CompetitionWork.user_id == user_id,
                CompetitionWork.competition_id == work.competition_id,
                CompetitionWork.release_status == 1,  # 只检查已提交的作品
                CompetitionWork.delete_time.is_(None)
            ).first()
            
            if existing_work:
                return {
                    "code": 400,
                    "msg": "您已经参加过该比赛，每个用户每个比赛只能参加一次",
                    "data": None
                }
        elif release_status == 2:  # 存为草稿状态
            # 草稿状态下，如果提供了比赛ID，也需要验证比赛是否存在
            if work.competition_id:
                competition = db.query(Competition).filter(
                    Competition.id == work.competition_id,
                    Competition.delete_time.is_(None)
                ).first()
                
                if not competition:
                    return {
                        "code": 404,
                        "msg": "比赛不存在",
                        "data": None
                    }
        
        # 创建作品对象，使用从token中获取的用户ID
        work_dict = work.dict()
        work_dict['user_id'] = user_id  # 覆盖请求中的user_id
        work_dict['work_views'] = 0  # 初始化浏览量为0
        work_dict['work_sort'] = 1  # 设置默认排序值为1
        work_dict['isPinned'] = False  # 设置默认不置顶
        work_dict['work_audit_status'] = 1 # 默认审核状态为1 审核中
        
        # 根据作品类型设置默认标签
        if work_dict.get('work_type') == '视频':
            work_dict['work_label'] = 'AI视频创业'
        elif work_dict.get('work_type') == '图片':
            work_dict['work_label'] = 'AI图片创业'
        else:
            work_dict['work_label'] = 'AI视频创业'  # 默认为视频标签
            
        db_work = CompetitionWork(**work_dict)
        
        # 保存到数据库
        db.add(db_work)
        db.commit()
        db.refresh(db_work)
        
        # 格式化datetime字段为字符串
        work_dict = {
            "id": db_work.id,
            "competition_id": db_work.competition_id,
            "user_id": db_work.user_id,
            "work_name": db_work.work_name,
            "work_description": db_work.work_description,
            "work_file": db_work.work_file,
            "work_type": db_work.work_type,
            "work_cover": db_work.work_cover,
            "work_status": db_work.work_status,
            "work_audit_status": db_work.work_audit_status,
            "work_sort": db_work.work_sort,
            "xhs_url": db_work.xhs_url,
            "dy_url": db_work.dy_url,
            "wb_url": db_work.wb_url,
            "sph_url": db_work.sph_url,
            "xhs_like": db_work.xhs_like,
            "dy_like": db_work.dy_like,
            "wb_like": db_work.wb_like,
            "sph_like": db_work.sph_like,
            "xhs_collect": db_work.xhs_collect,
            "dy_collect": db_work.dy_collect,
            "sph_collect": db_work.sph_collect,
            "xhs_comment": db_work.xhs_comment,
            "dy_comment": db_work.dy_comment,
            "wb_comment": db_work.wb_comment,
            "sph_comment": db_work.sph_comment,
            "wb_zf": db_work.wb_zf,
            "work_like": db_work.work_like,
            "work_comment": db_work.work_comment,
            "work_score": db_work.work_score,
            "work_rank": db_work.work_rank,
            "work_award": db_work.work_award,
            "work_views": db_work.work_views,  # 作品浏览量
            "work_label": db_work.work_label,  # 作品标签
            "wx_id": db_work.wx_id,
            "email": db_work.email,
            "release_status": db_work.release_status,
            "create_time": db_work.create_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.create_time else None,
            "update_time": db_work.update_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.update_time else None,
            "delete_time": db_work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.delete_time else None
        }
        
        return {
            "code": 200,
            "msg": "创建作品成功",
            "data": work_dict
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"创建作品失败: {str(e)}",
            "data": None
        }


@router.put("/works/{work_id}", summary="更新参赛作品")
async def update_competition_work(
    work_id: int, 
    work: CompetitionWorkUpdate, 
    db: Session = Depends(get_db)
):
    """
    更新参赛作品信息
    """
    try:
        # 查找作品
        db_work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not db_work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 更新字段
        update_data = work.dict(exclude_unset=True)
        
        # 如果更新了work_type，需要同时更新work_label
        if 'work_type' in update_data:
            if update_data['work_type'] == '视频':
                update_data['work_label'] = 'AI视频创业'
            elif update_data['work_type'] == '图片':
                update_data['work_label'] = 'AI图片创业'
            else:
                update_data['work_label'] = 'AI视频创业'  # 默认为视频标签
        
        for field, value in update_data.items():
            if field == 'work_cover':
                safe_replace_file(db_work, 'work_cover', value)
            else:
                setattr(db_work, field, value)
        
        db_work.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        db.refresh(db_work)
        
        # 格式化datetime字段为字符串
        work_dict = {
            "id": db_work.id,
            "competition_id": db_work.competition_id,
            "user_id": db_work.user_id,
            "work_name": db_work.work_name,
            "work_description": db_work.work_description,
            "work_file": db_work.work_file,
            "work_type": db_work.work_type,
            "work_cover": db_work.work_cover,
            "work_status": db_work.work_status,
            "work_audit_status": db_work.work_audit_status,
            "work_sort": db_work.work_sort,
            "xhs_url": db_work.xhs_url,
            "dy_url": db_work.dy_url,
            "wb_url": db_work.wb_url,
            "sph_url": db_work.sph_url,
            "xhs_like": db_work.xhs_like,
            "dy_like": db_work.dy_like,
            "wb_like": db_work.wb_like,
            "sph_like": db_work.sph_like,
            "xhs_collect": db_work.xhs_collect,
            "dy_collect": db_work.dy_collect,
            "sph_collect": db_work.sph_collect,
            "xhs_comment": db_work.xhs_comment,
            "dy_comment": db_work.dy_comment,
            "wb_comment": db_work.wb_comment,
            "sph_comment": db_work.sph_comment,
            "wb_zf": db_work.wb_zf,

            "work_like": db_work.work_like,
            "work_comment": db_work.work_comment,
            "work_score": db_work.work_score,
            "work_rank": db_work.work_rank,
            "work_award": db_work.work_award,
            "work_views": db_work.work_views,  # 作品浏览量
            "wx_id": db_work.wx_id,
            "email": db_work.email,
            "release_status": db_work.release_status,
            "create_time": db_work.create_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.create_time else None,
            "update_time": db_work.update_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.update_time else None,
            "delete_time": db_work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if db_work.delete_time else None
        }
        
        return {
            "code": 200,
            "msg": "更新作品成功",
            "data": work_dict
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"更新作品失败: {str(e)}",
            "data": None
        }


@router.delete("/works/{work_id}", summary="删除参赛作品")
async def delete_competition_work(work_id: int, db: Session = Depends(get_db)):
    """
    软删除参赛作品（设置删除时间）
    """
    try:
        # 查找作品
        db_work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not db_work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 软删除
        db_work.delete_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "作品删除成功",
            "data": None
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"删除作品失败: {str(e)}",
            "data": None
        }


@router.put("/works/{work_id}/release-status", summary="更新作品发布状态")
async def update_work_release_status(
    work_id: int,
    release_status: int,
    db: Session = Depends(get_db)
):
    """
    更新作品发布状态
    0-草稿 1-提交作品 2-存为草稿
    """
    try:
        # 查找作品
        db_work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not db_work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 验证发布状态值
        if release_status not in [0, 1, 2]:
            return {
                "code": 400,
                "msg": "无效的发布状态值，应为 0-草稿 1-提交作品 2-存为草稿",
                "data": None
            }
        
        # 更新发布状态
        db_work.release_status = release_status
        db_work.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "作品发布状态更新成功",
            "data": {
                "work_id": work_id,
                "release_status": release_status
            }
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"更新作品发布状态失败: {str(e)}",
            "data": None
        }


# ========== 作品互动功能接口 ==========

@router.post("/works/like", summary="作品点赞")
async def like_work(
    like_request: WorkLikeRequest,
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    给作品点赞接口
    - 从JWT token中获取当前用户ID
    - 从请求体中获取作品ID
    - 支持点赞和取消点赞功能，防止重复点赞
    - 创建或更新WorkLike记录，同时更新作品点赞数
    
    Args:
        like_request: 点赞请求（包含作品ID）
        db: 数据库会话
        credentials: 用户认证信息
    
    Returns:
        dict: 包含操作结果、点赞状态和点赞数的响应
    """
    try:
        # 从JWT token中获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 从请求体中获取作品ID
        work_id = like_request.work_id
        
        # 查找作品
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 查找用户是否已经对该作品有点赞记录
        existing_like = db.query(WorkLike).filter(
            WorkLike.work_id == work_id,
            WorkLike.user_id == current_user_id
        ).first()
        
        is_liked = False
        action_msg = ""
        
        if existing_like:
            # 如果已有记录，切换点赞状态
            if existing_like.is_like:
                # 当前是点赞状态，切换为取消点赞
                existing_like.is_like = False
                existing_like.update_time = datetime.now()
                work.work_like = max(0, work.work_like - 1)  # 确保点赞数不为负
                is_liked = False
                action_msg = "取消点赞成功"
            else:
                # 当前是取消点赞状态，切换为点赞
                existing_like.is_like = True
                existing_like.update_time = datetime.now()
                work.work_like += 1
                is_liked = True
                action_msg = "点赞成功"
        else:
            # 如果没有记录，创建新的点赞记录
            new_like = WorkLike(
                work_id=work_id,
                user_id=current_user_id,
                is_like=True,
                create_time=datetime.now(),
                update_time=datetime.now()
            )
            db.add(new_like)
            work.work_like += 1
            is_liked = True
            action_msg = "点赞成功"
        
        # 创建点赞通知（不给自己发通知，且只在点赞时发送）
        if is_liked and current_user_id != work.user_id:
            try:
                # 获取点赞用户信息
                liker_user = db.query(User).filter(User.id == current_user_id).first()
                liker_name = liker_user.username if liker_user else "未知用户"
                
                notification = UserNotification(
                    user_id=work.user_id,  # 作品作者
                    sender_id=current_user_id,  # 点赞用户
                    notification_type=1,  # 1-系统通知
                    title='作品点赞通知',
                    content=f'{liker_name} 点赞了你的作品《{work.work_name}》',
                    related_id=work_id,
                    related_type='work_like',
                    is_read_id=None,  # 使用is_read_id字段，None表示未读
                    is_delete_id=None  # 使用is_delete_id字段，None表示未删除
                )
                db.add(notification)
            except Exception as notification_error:
                # 通知创建失败不影响点赞功能
                print(f"创建点赞通知失败: {str(notification_error)}")
        
        # 更新作品的更新时间
        work.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": action_msg,
            "data": {
                "work_id": work_id,
                "user_id": current_user_id,
                "is_liked": is_liked,
                "like_count": work.work_like
            }
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"点赞操作失败: {str(e)}",
            "data": None
        }


@router.post("/works/{work_id}/score", summary="作品评分")
async def score_work(
    work_id: int, 
    score: int = Query(..., ge=0, le=100, description="评分 0-100"),
    judge_id: int = Query(..., description="评委ID"),
    db: Session = Depends(get_db)
):
    """
    评委给作品评分
    """
    try:
        # 查找作品
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 验证评委权限（这里简化处理，实际应该验证评委身份）
        judge = db.query(User).filter(User.id == judge_id, User.role == 3).first()
        if not judge:
            return {
                "code": 401,
                "msg": "无评委权限",
                "data": None
            }
        
        # 更新评分（这里简化处理，实际应该记录每个评委的评分）
        work.work_score = score
        work.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "评分成功",
            "data": {"score": work.work_score}
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"评分失败: {str(e)}",
            "data": None
        }


@router.put("/works/{work_id}/audit", summary="作品审核")
async def audit_work(
    work_id: int,
    audit_status: int = Query(..., description="审核状态 1-审核中 2-审核通过 3-审核不通过"),
    audit_reason: Optional[str] = Query(None, description="审核原因"),
    db: Session = Depends(get_db)
):
    """
    审核参赛作品
    """
    try:
        # 查找作品
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        # 更新审核状态
        work.work_audit_status = audit_status
        work.update_time = datetime.now()
        
        # 保存更改
        db.commit()
        
        return {
            "code": 200,
            "msg": "审核完成",
            "data": {"audit_status": audit_status}
        }
    
    except Exception as e:
        db.rollback()
        return {
            "code": 500,
            "msg": f"审核失败: {str(e)}",
            "data": None
        }


# ========== 统计和排名接口 ==========

@router.get("/competitions/{competition_id}/ranking", summary="获取比赛排名")
async def get_competition_ranking(
    competition_id: int,
    page: int = Query(1, ge=1, description="页码"),
    size: int = Query(10, ge=1, le=100, description="每页数量"),
    db: Session = Depends(get_db)
):
    """
    获取比赛作品排名
    按评分降序排列
    """
    try:
        # 验证比赛是否存在
        competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not competition:
            return {
                "code": 404,
                "msg": "比赛不存在",
                "data": None
            }
        
        # 查询该比赛的作品，按评分排序
        query = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None),
            CompetitionWork.work_audit_status == 2  # 只显示审核通过的作品
        ).order_by(desc(CompetitionWork.work_score), desc(CompetitionWork.work_like))
        
        # 获取总数
        total = query.count()
        
        # 分页
        offset = (page - 1) * size
        works = query.offset(offset).limit(size).all()
        
        # 计算总页数
        pages = math.ceil(total / size) if total > 0 else 1
        
        # 格式化datetime字段为字符串
        formatted_works = []
        for work in works:
            work_dict = {
                "id": work.id,
                "competition_id": work.competition_id,
                "user_id": work.user_id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_file": work.work_file,
                "work_type": work.work_type,
                "work_cover": work.work_cover,
                "work_status": work.work_status,
                "work_audit_status": work.work_audit_status,
                "work_sort": work.work_sort,
                "xhs_url": work.xhs_url,
                "dy_url": work.dy_url,
                "wb_url": work.wb_url,
                "sph_url": work.sph_url,
                "xhs_like": work.xhs_like,
                "dy_like": work.dy_like,
                "wb_like": work.wb_like,
                "sph_like": work.sph_like,
                "xhs_collect": work.xhs_collect,
                "dy_collect": work.dy_collect,
                "sph_collect": work.sph_collect,
                "xhs_comment": work.xhs_comment,
                "dy_comment": work.dy_comment,
                "wb_comment": work.wb_comment,
                "sph_comment": work.sph_comment,
                "wb_zf": work.wb_zf,

                "work_like": work.work_like,
                "work_comment": work.work_comment,
                "work_score": work.work_score,
                "work_rank": work.work_rank,
                "work_award": work.work_award,
                "wx_id": work.wx_id,
                "email": work.email,
                "release_status": work.release_status,
                "create_time": work.create_time.strftime("%Y-%m-%d %H:%M:%S") if work.create_time else None,
                "update_time": work.update_time.strftime("%Y-%m-%d %H:%M:%S") if work.update_time else None,
                "delete_time": work.delete_time.strftime("%Y-%m-%d %H:%M:%S") if work.delete_time else None
            }
            formatted_works.append(work_dict)
        
        return {
            "code": 200,
            "msg": "获取排名成功",
            "data": {
                "items": formatted_works,
                "total": total,
                "page": page,
                "size": size,
                "pages": pages
            }
        }
    
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取排名失败: {str(e)}",
            "data": None
        }


@router.get("/competitions/{competition_id}/stats", summary="获取比赛统计")
async def get_competition_stats(competition_id: int, db: Session = Depends(get_db)):
    """
    获取比赛统计信息
    """
    try:
        # 验证比赛是否存在
        competition = db.query(Competition).filter(
            Competition.id == competition_id,
            Competition.delete_time.is_(None)
        ).first()
        
        if not competition:
            return {
                "code": 404,
                "msg": "比赛不存在",
                "data": None
            }
        
        # 统计作品数量
        total_works = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None)
        ).count()
        
        # 统计审核通过的作品数量
        approved_works = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None),
            CompetitionWork.work_audit_status == 2
        ).count()
        
        # 统计待审核的作品数量
        pending_works = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None),
            CompetitionWork.work_audit_status == 1
        ).count()
        
        # 统计参赛用户数量
        participant_count = db.query(CompetitionWork.user_id).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.delete_time.is_(None)
        ).distinct().count()
        
        return {
            "code": 200,
            "msg": "获取统计信息成功",
            "data": {
                "competition_id": competition_id,
                "competition_name": competition.name,
                "total_works": total_works,
                "approved_works": approved_works,
                "pending_works": pending_works,
                "participant_count": participant_count,
                "competition_status": competition.competition_status
            }
        }
    
    except HTTPException:
        raise
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取统计信息失败: {str(e)}",
            "data": None
        }


@router.put("/works/{work_id}/sort", summary="置顶", response_model=WorkSortResponse)
async def update_work_sort(
    work_id: int,
    is_top: bool = Query(..., description="是否置顶，true为置顶，false为取消置顶"),
    db: Session = Depends(get_db)
):
    """
    更新作品排序值和置顶状态
    用于实现作品置顶功能
    - 置顶时自动计算当前最大排序值+1，并设置isPinned为True
    - 取消置顶时设置为1（普通作品默认值），并设置isPinned为False
    """
    try:
        # 查找作品
        work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not work:
            raise HTTPException(status_code=404, detail="作品不存在")
        
        if is_top:
            # 置顶：查询当前最大排序值并+1
            max_sort = db.query(func.max(CompetitionWork.work_sort)).filter(
                CompetitionWork.delete_time.is_(None)
            ).scalar() or 1
            work.work_sort = max_sort + 1
            work.isPinned = True  # 设置置顶状态为True
            msg = "置顶成功"
        else:
            # 取消置顶：设置为默认值1
            work.work_sort = 1
            work.isPinned = False  # 设置置顶状态为False
            msg = "取消置顶成功"
        
        work.update_time = datetime.now()
        
        # 提交更改
        db.commit()
        db.refresh(work)
        
        return WorkSortResponse(
            code=200,
            msg=msg,
            data={
                "work_id": work_id,
                "work_sort": work.work_sort,
                "work_name": work.work_name,
                "is_top": is_top,
                "isPinned": work.isPinned
            }
        )
    
    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        return WorkSortResponse(
            code=500,
            msg=f"更新排序失败: {str(e)}",
            data=None
        )


# ==================== 评论相关接口 ====================

@router.post("/comments", summary="发布评论")
async def create_comment(
    comment: WorkCommentCreate,
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    发布评论接口
    支持发布顶级评论和回复评论
    
    Args:
        comment: 评论创建请求（包含作品ID、评论内容和父评论ID）
        db: 数据库会话
        credentials: 用户认证信息
    
    Returns:
        WorkCommentResponse: 包含新创建评论信息的响应
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 敏感词检测
        is_sensitive, sensitive_msg = await check_sensitive_content(comment.content)
        print(f"敏感词检测结果: {is_sensitive}, 原因: {sensitive_msg}")
        if not is_sensitive:
            return {
                "code": 400,
                "msg": f"评论内容包含敏感词: {sensitive_msg}",
                "data": None
            }
        
        # 从请求体中获取作品ID
        work_id = comment.work_id
        
        # 检查作品是否存在
        work = db.query(CompetitionWork).filter(CompetitionWork.id == work_id).first()
        if not work:
            raise HTTPException(status_code=404, detail="作品不存在")
        
        # 如果是回复评论，检查父评论是否存在
        if comment.parent_id:
            parent_comment = db.query(WorkComment).filter(WorkComment.id == comment.parent_id).first()
            if not parent_comment:
                raise HTTPException(status_code=404, detail="父评论不存在")
            # 确保父评论属于同一作品
            if parent_comment.work_id != work_id:
                raise HTTPException(status_code=400, detail="父评论不属于当前作品")
        
        # 获取用户信息并检查用户是否存在
        user = db.query(User).filter(User.id == current_user_id).first()
        if not user:
            raise HTTPException(status_code=404, detail="用户不存在")
        
        # 获取回复目标用户昵称（如果是子评论）
        reply_name = None
        if comment.parent_id:
            # 从父评论中获取被回复用户的昵称
            parent_comment = db.query(WorkComment).filter(WorkComment.id == comment.parent_id).first()
            if parent_comment:
                parent_user = db.query(User).filter(User.id == parent_comment.user_id).first()
                if parent_user:
                    reply_name = parent_user.nickname if parent_user.nickname else parent_user.username
                else:
                    reply_name = "未知用户"
        
        # 创建评论
        new_comment = WorkComment(
            work_id=work_id,
            user_id=current_user_id,
            parent_id=comment.parent_id,
            content=comment.content,
            reply_name=reply_name  # 设置回复目标用户昵称
        )
        
        db.add(new_comment)
        db.flush()  # 获取新创建的评论ID
        
        # 创建评论通知（不给自己发通知）
        if current_user_id != work.user_id:
            # 获取评论用户信息
            commenter = db.query(User).filter(User.id == current_user_id).first()
            commenter_name = commenter.username if commenter else "未知用户"
            
            notification = UserNotification(
                user_id=work.user_id,  # 作品作者
                sender_id=current_user_id,  # 评论者
                notification_type=1,  # 1-系统通知
                title='作品评论通知',
                content=f'{commenter_name} 评论了你的作品《{work.work_name}》：{comment.content[:50]}{"..." if len(comment.content) > 50 else ""}',
                related_id=new_comment.id,
                related_type='comment',
                is_read_id=None,  # 使用is_read_id字段，None表示未读
                is_delete_id=None  # 使用is_delete_id字段，None表示未删除
            )
            db.add(notification)
        db.commit()
        db.refresh(new_comment)
        
        # 构建响应数据 - 返回标准格式 {code, msg, data}
        comment_data = {
            "id": new_comment.id,
            "work_id": new_comment.work_id,
            "user_id": new_comment.user_id,
            "user_nickname": user.nickname if user.nickname else user.username if user.username else "未知用户",
            "user_avatar": user.avatar if user.avatar else "",
            "parent_id": new_comment.parent_id,
            "content": new_comment.content,
            "like_count": new_comment.like_count,
            "reply_count": 0,  # 新创建的评论默认回复数为0
            "is_delete": False,
            "delete_time": None,
            "create_time": new_comment.create_time if new_comment.create_time else datetime.now(),
            "update_time": new_comment.update_time if new_comment.update_time else datetime.now(),
            "reply_name": reply_name if reply_name else "",  # 回复目标用户昵称
            "replies": []  # 新创建的评论默认无子评论
        }
        
        return {
            "code": 200,
            "msg": "评论发布成功",
            "data": comment_data
        }
        
    except HTTPException as http_exc:
        # 处理HTTP异常，返回标准格式
        return {
            "code": http_exc.status_code,
            "msg": http_exc.detail,
            "data": None
        }
    except Exception as e:
        # 回滚数据库事务
        db.rollback()
        # 返回标准格式的错误响应
        return {
            "code": 500,
            "msg": f"发布评论失败: {str(e)}",
            "data": None
        }


@router.get("/works/{work_id}/comments", summary="获取作品评论列表", response_model=WorkCommentListUnifiedResponse)
async def get_work_comments(
    work_id: int,
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(10, ge=1, le=100, description="每页数量"),
    db: Session = Depends(get_db),
    credentials: Optional[HTTPAuthorizationCredentials] = Depends(security)
):
    """
    获取作品评论列表接口（嵌套结构）
    - work_id: 作品ID
    - 返回包含子评论的嵌套结构
    - 如果用户已登录，返回用户对每个评论的点赞状态
    """
    try:
        # 获取当前用户ID（如果已登录）
        current_user_id = None
        if credentials:
            try:
                current_user_id = get_current_user_id(credentials)
            except HTTPException:
                # 如果token无效，继续执行但不返回点赞状态
                current_user_id = None
        
        # 检查作品是否存在
        work = db.query(CompetitionWork).filter(CompetitionWork.id == work_id).first()
        if not work:
            return WorkCommentListUnifiedResponse(
                code=200,
                msg="获取评论列表成功",
                data=WorkCommentListResponse(
                    items=[],
                    total=0,
                    page=pageNum,
                    size=pageSize,
                    pages=0
                )
            )
        
        # 获取该作品的所有评论（包括父评论和子评论）
        all_comments = db.query(WorkComment).filter(
            WorkComment.work_id == work_id
        ).order_by(desc(WorkComment.create_time)).all()
        
        # 如果有评论且用户已登录，批量查询用户的点赞状态
        user_likes = {}
        if all_comments and current_user_id:
            comment_ids = [comment.id for comment in all_comments]
            like_records = db.query(CommentLike).filter(
                CommentLike.comment_id.in_(comment_ids),
                CommentLike.user_id == current_user_id,
                CommentLike.is_like == True
            ).all()
            user_likes = {like.comment_id: True for like in like_records}
        
        # 批量查询所有评论用户信息，减少N+1查询
        user_ids = list(set([comment.user_id for comment in all_comments]))
        users_dict = {}
        if user_ids:
            users = db.query(User).filter(User.id.in_(user_ids)).all()
            users_dict = {user.id: user for user in users}
        
        # 构建评论字典，方便查找
        comment_dict = {}
        parent_comments = []  # 顶级评论列表
        
        # 第一遍遍历：构建所有评论的基础数据
        for comment in all_comments:
            # 获取用户信息
            user = users_dict.get(comment.user_id)
            
            # 获取回复目标用户昵称（对于子评论，显示被回复的父评论作者昵称）
            reply_name = ""
            if comment.parent_id and comment.parent_id != 0:
                # 优先从数据库中读取 reply_name 字段
                if hasattr(comment, 'reply_name') and comment.reply_name:
                    reply_name = comment.reply_name
                else:
                    # 如果数据库中的 reply_name 为空，动态查询父评论作者昵称
                    parent_comment_obj = next((c for c in all_comments if c.id == comment.parent_id), None)
                    if parent_comment_obj:
                        parent_user = users_dict.get(parent_comment_obj.user_id)
                        reply_name = parent_user.username if parent_user else "未知用户"
            
            comment_data = {
                "id": comment.id,
                "work_id": comment.work_id,
                "user_id": comment.user_id,
                "user_nickname": user.username if user else "未知用户",
                "parent_id": comment.parent_id,
                "content": comment.content,
                "like_count": comment.like_count,
                "reply_count": 0,  # 稍后计算
                "user_avatar": user.avatar if user and user.avatar else "",
                "is_delete": False,
                "create_time": comment.create_time,
                "update_time": comment.update_time,
                "delete_time": None,
                "is_liked": user_likes.get(comment.id, False),  # 用户点赞状态
                "reply_name": reply_name,  # 被回复人昵称，仅对子评论有效
                "replies": []  # 子评论列表
            }
            
            comment_dict[comment.id] = comment_data
            
            # 如果是顶级评论（parent_id为空或为0）
            if not comment.parent_id or comment.parent_id == 0:
                parent_comments.append(comment_data)
        
        # 第二遍遍历：构建嵌套结构
        for comment in all_comments:
            if comment.parent_id and comment.parent_id != 0:
                # 这是子评论，将其添加到父评论的replies中
                parent_comment = comment_dict.get(comment.parent_id)
                if parent_comment:
                    parent_comment["replies"].append(comment_dict[comment.id])
        
        # 第三遍遍历：计算每个父评论的回复数量
        for parent_comment in parent_comments:
            parent_comment["reply_count"] = len(parent_comment["replies"])
            # 对子评论按时间排序
            parent_comment["replies"].sort(key=lambda x: x["create_time"], reverse=True)
        
        # 对顶级评论按时间排序
        parent_comments.sort(key=lambda x: x["create_time"], reverse=True)
        
        # 分页处理（只对顶级评论分页）
        total_parent_comments = len(parent_comments)
        offset = (pageNum - 1) * pageSize
        paginated_comments = parent_comments[offset:offset + pageSize]
        
        # 计算分页信息
        total_pages = math.ceil(total_parent_comments / pageSize)
        
        # 计算总评论数（包含所有子评论）
        total_all_comments = len(all_comments)
        print(f"总评论数（包含子评论）: {total_all_comments}")
        
        # 构建响应数据
        response_data = WorkCommentListResponse(
            items=paginated_comments,
            total=total_all_comments,  # 返回包含子评论的总数
            page=pageNum,
            size=pageSize,
            pages=total_pages
        )
        
        # 返回统一格式
        return WorkCommentListUnifiedResponse(
            code=200,
            msg="获取评论列表成功",
            data=response_data
        )
        
    except Exception as e:
        # 异常情况下返回错误信息
        empty_data = WorkCommentListResponse(
            items=[],
            total=0,
            page=pageNum,
            size=pageSize,
            pages=0
        )
        
        return WorkCommentListUnifiedResponse(
            code=500,
            msg=f"获取评论列表失败: {str(e)}",
            data=empty_data
        )


@router.delete("/comments", summary="删除评论", response_model=CommentResponse)
async def delete_comment(
    delete_request: CommentDeleteRequest,
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    删除评论接口
    支持递归删除评论及其所有子评论
    
    Args:
        delete_request: 删除请求（包含评论ID）
        db: 数据库会话
        credentials: 用户认证信息
    
    Returns:
        CommentResponse: 包含操作结果的响应
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 从请求体中获取评论ID
        comment_id = delete_request.comment_id
        
        # 查找评论
        comment = db.query(WorkComment).filter(WorkComment.id == comment_id).first()
        if not comment:
            return CommentResponse(
                code=404,
                msg="评论不存在",
                data=None
            )
        
        # 检查权限（只能删除自己的评论）
        if comment.user_id != current_user_id:
            return CommentResponse(
                code=403,
                msg="无权限删除此评论",
                data=None
            )
        
        # 删除评论及其所有子评论
        def delete_comment_recursive(comment_id):
            # 先删除所有子评论
            child_comments = db.query(WorkComment).filter(WorkComment.parent_id == comment_id).all()
            for child in child_comments:
                delete_comment_recursive(child.id)
            
            # 删除当前评论
            db.query(WorkComment).filter(WorkComment.id == comment_id).delete()
        
        delete_comment_recursive(comment_id)
        db.commit()
        
        return CommentResponse(
            code=200,
            msg="评论删除成功",
            data=None
        )
        
    except Exception as e:
        db.rollback()
        return CommentResponse(
            code=500,
            msg=f"删除评论失败: {str(e)}",
            data=None
        )


@router.post("/comments/like", summary="评论点赞", response_model=CommentResponse)
async def like_comment(
    like_request: CommentLikeRequest,
    db: Session = Depends(get_db),
    credentials: HTTPAuthorizationCredentials = Depends(security)
):
    """
    评论点赞接口
    支持点赞和取消点赞功能，已经点赞的再传入则是取消点赞
    点赞时会向评论作者发送通知，取消点赞时会移除通知
    
    Args:
        like_request: 点赞请求（包含评论ID和点赞状态）
        db: 数据库会话
        credentials: 用户认证信息
    
    Returns:
        CommentResponse: 包含操作结果和点赞状态的响应
    """
    try:
        # 获取当前用户ID
        current_user_id = get_current_user_id(credentials)
        
        # 从请求体中获取评论ID
        comment_id = like_request.comment_id
        
        # 查找评论
        comment = db.query(WorkComment).filter(WorkComment.id == comment_id).first()
        if not comment:
            return CommentResponse(
                code=404,
                msg="评论不存在",
                data=None
            )
        
        # 查找用户的点赞记录
        like_record = db.query(CommentLike).filter(
            CommentLike.comment_id == comment_id,
            CommentLike.user_id == current_user_id
        ).first()
        
        # 获取点赞用户信息（用于通知）
        liker_user = db.query(User).filter(User.id == current_user_id).first()
        liker_name = liker_user.username if liker_user else "未知用户"
        
        if like_request.is_like:
            # 用户要点赞
            if like_record:
                if like_record.is_like:
                    # 已经点赞了，返回提示
                    return CommentResponse(
                        code=200,
                        msg="您已经点赞过了",
                        data={
                            "comment_id": comment_id,
                            "like_count": comment.like_count,
                            "is_liked": True
                        }
                    )
                else:
                    # 之前取消过点赞，现在重新点赞
                    like_record.is_like = True
                    like_record.update_time = datetime.now()
                    comment.like_count += 1
                    msg = "点赞成功"
                    
                    # 创建点赞通知（不给自己发通知）
                    if current_user_id != comment.user_id:
                        try:
                            notification = UserNotification(
                                user_id=comment.user_id,  # 评论作者
                                sender_id=current_user_id,  # 点赞用户
                                notification_type=1,  # 1-系统通知
                                title='评论点赞通知',
                                content=f'{liker_name} 点赞了你的评论：{comment.content[:30]}{"..." if len(comment.content) > 30 else ""}',
                                related_id=comment_id,
                                related_type='comment_like',
                                is_read_id=None,  # 使用is_read_id字段，None表示未读
                                is_delete_id=None  # 使用is_delete_id字段，None表示未删除
                            )
                            db.add(notification)
                        except Exception as notification_error:
                            # 通知创建失败不影响点赞功能
                            print(f"创建点赞通知失败: {str(notification_error)}")
            else:
                # 第一次点赞，创建新记录
                new_like = CommentLike(
                    comment_id=comment_id,
                    user_id=current_user_id,
                    is_like=True
                )
                db.add(new_like)
                comment.like_count += 1
                msg = "点赞成功"
                
                # 创建点赞通知（不给自己发通知）
                if current_user_id != comment.user_id:
                    try:
                        notification = UserNotification(
                            user_id=comment.user_id,  # 评论作者
                            sender_id=current_user_id,  # 点赞用户
                            notification_type=1,  # 1-系统通知
                            title='评论点赞通知',
                            content=f'{liker_name} 点赞了你的评论：{comment.content[:30]}{"..." if len(comment.content) > 30 else ""}',
                            related_id=comment_id,
                            related_type='comment_like',
                            is_read_id=None,  # 使用is_read_id字段，None表示未读
                            is_delete_id=None  # 使用is_delete_id字段，None表示未删除
                        )
                        db.add(notification)
                    except Exception as notification_error:
                        # 通知创建失败不影响点赞功能
                        print(f"创建点赞通知失败: {str(notification_error)}")
        else:
            # 用户要取消点赞
            if like_record and like_record.is_like:
                # 之前点赞了，现在取消点赞
                like_record.is_like = False
                like_record.update_time = datetime.now()
                comment.like_count = max(0, comment.like_count - 1)  # 确保不会小于0
                msg = "取消点赞成功"
                
                # 删除对应的点赞通知
                try:
                    # 查找并删除相关的点赞通知
                    notification_to_delete = db.query(UserNotification).filter(
                        UserNotification.user_id == comment.user_id,
                        UserNotification.sender_id == current_user_id,
                        UserNotification.notification_type == 1,
                        UserNotification.related_id == comment_id,
                        UserNotification.related_type == 'comment_like',
                        UserNotification.is_delete == False
                    ).first()
                    
                    if notification_to_delete:
                        # 软删除通知
                        notification_to_delete.is_delete = True
                        notification_to_delete.delete_time = datetime.now()
                except Exception as notification_error:
                    # 通知删除失败不影响取消点赞功能
                    print(f"删除点赞通知失败: {str(notification_error)}")
            else:
                # 没有点赞记录或已经取消过点赞
                return CommentResponse(
                    code=200,
                    msg="您还没有点赞过",
                    data={
                        "comment_id": comment_id,
                        "like_count": comment.like_count,
                        "is_liked": False
                    }
                )
        
        # 更新评论的更新时间
        comment.update_time = datetime.now()
        
        # 提交事务
        db.commit()
        
        return CommentResponse(
            code=200,
            msg=msg,
            data={
                "comment_id": comment_id,
                "like_count": comment.like_count,
                "is_liked": like_request.is_like
            }
        )
        
    except Exception as e:
        db.rollback()
        return CommentResponse(
            code=500,
            msg=f"点赞操作失败: {str(e)}",
            data=None
        )


# ==================== 相关作品推荐 ====================

@router.get("/works/{work_id}/recommendations", summary="获取相关作品推荐")
async def get_work_recommendations(
    work_id: int,
    pageNum: int = Query(1, ge=1, description="页码"),
    pageSize: int = Query(5, ge=1, le=50, description="每页数量"),
    db: Session = Depends(get_db)
):
    """
    根据作品ID获取相关作品推荐
    
    推荐逻辑：
    1. 查询该作品所属的比赛
    2. 获取该比赛中的其他作品（排除当前作品）
    3. 优先推荐已审核通过的作品（work_audit_status=2）
    4. 按作品评分从高到低排序（work_score DESC）
    5. 支持分页，默认返回10条推荐
    
    Args:
        work_id: 作品ID
        pageNum: 页码，默认为1
        pageSize: 每页数量，默认为10，最大50
        db: 数据库会话
    
    Returns:
        包含推荐作品列表的响应数据
    """
    try:
        # 1. 根据work_id查询该作品所属的比赛
        current_work = db.query(CompetitionWork).filter(
            CompetitionWork.id == work_id,
            CompetitionWork.delete_time.is_(None)
        ).first()
        
        if not current_work:
            return {
                "code": 404,
                "msg": "作品不存在",
                "data": None
            }
        
        competition_id = current_work.competition_id
        
        # 2. 构建查询：获取该比赛中的其他作品（排除当前作品）
        query = db.query(CompetitionWork).filter(
            CompetitionWork.competition_id == competition_id,
            CompetitionWork.id != work_id,  # 排除当前作品
            CompetitionWork.delete_time.is_(None),
        )
        
        # 3. 推荐排序逻辑：优先已审核，然后按分数降序
        # 使用简化的排序方式，避免复杂的CASE WHEN语句
        query = query.order_by(
            # 第一优先级：审核状态（2-审核通过优先，使用负号让2排在前面）
            desc(CompetitionWork.work_audit_status == 2),
            # 第二优先级：作品评分降序
            desc(CompetitionWork.work_score),
            # 第三优先级：创建时间降序（最新的优先）
            desc(CompetitionWork.create_time)
        )
        
        # 获取总数
        total = query.count()
        
        # 4. 分页查询
        offset = (pageNum - 1) * pageSize
        recommended_works = query.offset(offset).limit(pageSize).all()
        
        # 5. 格式化返回数据
        recommendations = []
        for work in recommended_works:
            # 查询作者信息
            author = db.query(User).filter(User.id == work.user_id).first()
            author_name = author.username if author else "未知用户"
            
            work_data = {
                "id": work.id,
                "work_name": work.work_name,
                "work_description": work.work_description,
                "work_cover": work.work_cover,
                "work_type": work.work_type,
                "work_score": work.work_score,
                "work_audit_status": work.work_audit_status,
                "work_like": work.work_like,
                "work_views": work.work_views,
                "work_award": work.work_award,
                "author_name": author_name,
                "create_time": work.create_time.strftime("%Y-%m-%d %H:%M:%S") if work.create_time else None
            }
            recommendations.append(work_data)
        
        # 计算总页数
        pages = math.ceil(total / pageSize) if total > 0 else 1
        
        return {
            "code": 200,
            "msg": "获取推荐作品成功",
            "data": {
                "current_work_id": work_id,
                "competition_id": competition_id,
                "recommendations": recommendations,
                "total": total,
                "pageNum": pageNum,
                "pageSize": pageSize,
                "pages": pages
            }
        }
        
    except Exception as e:
        return {
            "code": 500,
            "msg": f"获取推荐作品失败: {str(e)}",
            "data": None
        }
@router.get("/competitions/judges", summary="查询当前登录用户担任评委的比赛列表")
async def get_my_judge_competitions(include_ended: bool = False, db: Session = Depends(get_db), credentials: Optional[HTTPAuthorizationCredentials] = Depends(HTTPBearer(auto_error=False))):
    """
    查询当前登录用户作为评委的比赛列表（需登录）

    Args:
        include_ended: 是否包含已截止比赛（默认不包含）
        db: 数据库会话

    Returns:
        {code, msg, data: [{id, name}]}
    """
    if not credentials or not getattr(credentials, 'credentials', None):
        raise HTTPException(status_code=401, detail="未认证")
    uid = get_current_user_id(credentials)
    q = db.query(Competition.id, Competition.name, Competition.judge_ids).filter(Competition.competition_status == 1)
    if not include_ended:
        q = q.filter(or_(Competition.competition_end_time.is_(None), Competition.competition_end_time > datetime.now()))
    rows = q.all()
    res = []
    for cid, cname, jids in rows:
        if not jids:
            continue
        raw = str(jids).strip()
        parts = [p.strip() for p in raw.split(',') if p and p.strip()]
        ids_set = set()
        for p in parts:
            try:
                ids_set.add(int(p))
            except Exception:
                pass
        if uid in ids_set:
            res.append({"id": cid, "name": cname})
    return {"code": 200, "msg": "success", "data": res}


# 返回比赛评分规则

@router.post("/competitions/{competition_id}/works/{work_id}/rate-dimensions", summary="裁判维度评分")
async def rate_work_by_dimensions(
    competition_id: int,
    work_id: int,
    body: ScoreCreate,
    db: Session = Depends(get_db),
    credentials: Optional[HTTPAuthorizationCredentials] = Depends(HTTPBearer(auto_error=False))
):
    """
    裁判为作品进行维度化评分（0.5-5，支持小数），并按维度权重计算加权总分

    Args:
        competition_id: 比赛ID
        work_id: 作品ID
        body: 维度评分请求体
        db: 数据库会话
        credentials: 鉴权凭据

    Returns:
        评分结果与加权总分
    """
    if not credentials or not getattr(credentials, 'credentials', None):
        raise HTTPException(status_code=401, detail="未认证")
    uid = get_current_user_id(credentials)
    # 确保评分表已创建（不存在时自动创建）
    try:
        WorkJudgeDimensionScore.__table__.create(bind=engine, checkfirst=True)
    except Exception:
        pass

    comp = db.query(Competition).filter(Competition.id == competition_id, Competition.delete_time.is_(None)).first()
    if not comp:
        raise HTTPException(status_code=404, detail="比赛不存在")
    work = db.query(CompetitionWork).filter(CompetitionWork.id == work_id, CompetitionWork.competition_id == competition_id, CompetitionWork.delete_time.is_(None)).first()
    if not work:
        raise HTTPException(status_code=404, detail="作品不存在")
    raw = str(comp.judge_ids or '').strip()
    parts = [p.strip() for p in raw.split(',') if p and p.strip()]
    judge_set = set()
    for p in parts:
        try:
            judge_set.add(int(p))
        except Exception:
            pass
    if uid not in judge_set:
        raise HTTPException(status_code=403, detail="非该比赛评委，无权评分")
    dims = db.query(ScoreDimensions).all()
    valid_dims = {d.id: float(d.dimension_weight) for d in dims}
    if not valid_dims:
        raise HTTPException(status_code=400, detail="评分维度未配置")
    for item in body.scores:
        if item.dimension_id not in valid_dims:
            raise HTTPException(status_code=400, detail=f"非法维度ID: {item.dimension_id}")
        if item.rate < 0.5 or item.rate > 5.0:
            raise HTTPException(status_code=400, detail="评分范围应为0.5-5")
    for item in body.scores:
        rec = db.query(WorkJudgeDimensionScore).filter(
            WorkJudgeDimensionScore.work_id == work_id,
            WorkJudgeDimensionScore.judge_id == uid,
            WorkJudgeDimensionScore.dimension_id == item.dimension_id
        ).first()
        if rec:
            rec.rate = item.rate
            rec.comment = item.comment
            rec.update_time = datetime.now()
        else:
            rec = WorkJudgeDimensionScore(
                competition_id=competition_id,
                work_id=work_id,
                judge_id=uid,
                dimension_id=item.dimension_id,
                rate=item.rate,
                comment=item.comment,
                create_time=datetime.now(),
                update_time=datetime.now()
            )
            db.add(rec)
    db.commit()
    # 直接使用“权重 × 分数 × 0.2”的规则按本次提交计算总分
    weights = valid_dims
    total = 0.0
    breakdown = []
    for s in body.scores:
        w = weights.get(s.dimension_id, 0.0)
        contribution = w * float(s.rate) * 0.2
        total += contribution
        breakdown.append({"dimension_id": s.dimension_id, "weight": w, "rate": float(s.rate), "contribution": round(contribution, 2)})
    weighted_score = total
    work.work_score = int(round(weighted_score))
    db.commit()
    return {"code": 200, "msg": "评分成功", "data": {"weighted_score": round(weighted_score, 2), "rate_count": len(body.scores), "dimensions": [{"dimension_id": s.dimension_id, "rate": s.rate} for s in body.scores], "weights_breakdown": breakdown}}



# 返回比赛评分规则（不分页，默认返回全部维度及规则，结构与 /v1/score-dimensions/rules/all 一致）
@router.get("/competitions/score-rules", summary="获取比赛评分维度与规则（全部）")
async def get_competition_score_rules(db: Session = Depends(get_db)):
    """
    获取全部评分维度及其对应的规则（不分页、不筛选），
    返回结构与 /v1/score-dimensions/rules/all 接口保持一致。

    Returns:
        {code, msg, data: {dimensions_with_rules, pagination, summary}}
    """
    try:
        # 查询所有维度，按ID升序
        dimensions = db.query(ScoreDimensions).order_by(ScoreDimensions.id.asc()).all()
        total_dimensions = len(dimensions)

        dimensions_with_rules = []
        total_rules_count = 0

        for dimension in dimensions:
            # 查询该维度的所有评分规则，按评分等级升序
            rules = db.query(DimensionScoreRules).filter(
                DimensionScoreRules.dimension_id == dimension.id
            ).order_by(DimensionScoreRules.score_level.asc()).all()
            total_rules_count += len(rules)

            # 组装规则数据
            dimension_rules = [
                {
                    "id": rule.id,
                    "dimension_id": rule.dimension_id,
                    "score_level": rule.score_level,
                    "level_desc": rule.level_desc,
                    "judge_criteria": rule.judge_criteria,
                    "create_time": rule.create_time.strftime("%Y-%m-%d %H:%M:%S") if rule.create_time else None
                }
                for rule in rules
            ]

            dimensions_with_rules.append({
                "dimension_info": {
                    "id": dimension.id,
                    "dimension_name": dimension.dimension_name,
                    "description": dimension.dimension_desc,
                    "weight": float(dimension.dimension_weight),
                    "create_time": dimension.create_time.strftime("%Y-%m-%d %H:%M:%S") if dimension.create_time else None
                },
                "rules": dimension_rules,
                "rules_count": len(dimension_rules)
            })

        # 构建与原接口一致的响应结构（不分页，固定分页信息）
        response_data = {
            "dimensions_with_rules": dimensions_with_rules,
            "pagination": {
                "current_page": 1,
                "page_size": total_dimensions,
                "total_dimensions": total_dimensions,
                "total_pages": 1
            },
            "summary": {
                "total_dimensions": total_dimensions,
                "current_page_dimensions": total_dimensions,
                "total_rules": total_rules_count
            }
        }

        return {"code": 200, "msg": "查询成功", "data": response_data}
    except Exception as e:
        return {"code": 500, "msg": f"查询失败: {str(e)}"}
